在 SELF JOIN (SQL) 中难以生成没有重复项的不同行

发布于 2025-01-10 20:12:06 字数 1321 浏览 0 评论 0原文

我对 SQL 还很陌生，我认为我可以使用它为我雇主的客户创建一个列表。不幸的是，大多数客户都有多个帐户，并且文件中每个帐户都有一个不同的行。

我试图使用自联接为每个客户创建一行，并为帐户创建多列。

SELECT DISTINCT A.Account_Number AS Account_1, B.Account_Number AS Account_2, A.Client_Name
FROM client_table AS A, client_table AS B
WHERE A.Account_Number <> B.Account_Number
AND A.Client_Name = B.Client_Name
ORDER BY A.Client_Name;

不幸的是，结果是这样的，我会得到一个如下所示的表：

Account_1	Account_2	Client_name
000001	000002	Joe Shmo
000001	000003	Joe Shmo
000002	000003	Joe Shmo
000002	000001	Joe Shmo

我知道对于两个以上的帐户，我需要超过两次加入，但我还没有知道怎么做了。

有没有办法防止重复输入？

顺便说一句，我正在使用 BigQuery。

原文

I'm pretty new to SQL and thought I'd be able to use it to create a list for my employer's clients. Unfortunately, there the majority of clients have more than one account and the file has a distinct row for each account.

I was trying to use a self join to create one row for each client with multiple columns for the accounts.

SELECT DISTINCT A.Account_Number AS Account_1, B.Account_Number AS Account_2, A.Client_Name
FROM client_table AS A, client_table AS B
WHERE A.Account_Number <> B.Account_Number
AND A.Client_Name = B.Client_Name
ORDER BY A.Client_Name;

Unfortunately, the results were such that I would get a table that looks like:

Account_1	Account_2	Client_name
000001	000002	Joe Shmo
000001	000003	Joe Shmo
000002	000003	Joe Shmo
000002	000001	Joe Shmo

I understand that for more than two accounts, I'll need more than two joins, but I haven't figured out how to do it.

Is there a way to prevent double entry?

I'm using BigQuery btw.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

混浊又暗下来 2025-01-17 20:12:06

第一步对每个客户的账户进行编号。然后使用条件聚合为每个帐户获取一列。

select 
  client_name,
  max(case when rn = 1 then account_number end) as account1,
  max(case when rn = 2 then account_number end) as account2,
  max(case when rn = 3 then account_number end) as account3,
  max(case when rn = 4 then account_number end) as account4,
  max(case when rn = 5 then account_number end) as account5,
  max(case when rn = 6 then account_number end) as account6
from
(
  select
    client_name,
    account_number,
    row_number() over (partition by client_name order by account_number) as rn
  from client_table
) numbered
group by client_name
order by client_name;

In the first step number the accounts per client. Then use conditional aggregation to get one column per account.

select 
  client_name,
  max(case when rn = 1 then account_number end) as account1,
  max(case when rn = 2 then account_number end) as account2,
  max(case when rn = 3 then account_number end) as account3,
  max(case when rn = 4 then account_number end) as account4,
  max(case when rn = 5 then account_number end) as account5,
  max(case when rn = 6 then account_number end) as account6
from
(
  select
    client_name,
    account_number,
    row_number() over (partition by client_name order by account_number) as rn
  from client_table
) numbered
group by client_name
order by client_name;

回复收藏 0 原文

水中月 2025-01-17 20:12:06

您可以使用LEFT JOIN来完成此操作。根据需要对用户可能拥有的多个帐户重复此操作。

SELECT DISTINCT ct.Client_Name, ct1.Account_Number AS Account_1, ct2.Account_Number AS Account_2,
                ct3.Account_Number AS Account_3, ct4.Account_Number AS Account_4
FROM client_table ct
LEFT JOIN client_table ct1 ON ct1.Client_Name = ct.Client_Name AND ct1.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name)
LEFT JOIN client_table ct2 ON ct2.Client_Name = ct.Client_Name AND ct2.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct1.Account_Number)
LEFT JOIN client_table ct3 ON ct3.Client_Name = ct.Client_Name AND ct3.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct2.Account_Number)
LEFT JOIN client_table ct4 ON ct4.Client_Name = ct.Client_Name AND ct4.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct3.Account_Number)

You can do this with LEFT JOIN. Repeat as necessary for however many accounts a user may have.

SELECT DISTINCT ct.Client_Name, ct1.Account_Number AS Account_1, ct2.Account_Number AS Account_2,
                ct3.Account_Number AS Account_3, ct4.Account_Number AS Account_4
FROM client_table ct
LEFT JOIN client_table ct1 ON ct1.Client_Name = ct.Client_Name AND ct1.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name)
LEFT JOIN client_table ct2 ON ct2.Client_Name = ct.Client_Name AND ct2.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct1.Account_Number)
LEFT JOIN client_table ct3 ON ct3.Client_Name = ct.Client_Name AND ct3.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct2.Account_Number)
LEFT JOIN client_table ct4 ON ct4.Client_Name = ct.Client_Name AND ct4.Account_Number = (SELECT MIN(Account_Number) FROM client_table WHERE Client_Name = ct.Client_Name AND Account_Number > ct3.Account_Number)

回复收藏 0 原文

尸血腥色 2025-01-17 20:12:06

您可以使用 GROUP_CONCAT() 获取逗号分隔的列表。
或者，您可以使用 RANK() 在 CTE 中对您的帐户进行排序，然后列出它们。
注意：任何第 7 个或以上的帐户都不会显示！

创建表客户端（
cname varchar(10),
帐户字符(6));
插入客户值
('乔·施莫','000001'),
('乔·施莫','000002'),
('乔·施莫','000003'),
('乔·施莫','000004');

<前><代码>选择
cname“名称”，
group_concat(帐户) 帐户编号
来自客户
按 CNAME 分组
按 cname 排序；

<前>
名称 |帐号
:----- | :--------------------------
乔什莫 | 000001,000002,000003,000004

以 c 为 
（选择
域名,
帐户，
rank() over（按cname分区，按账户排序）排名
来自客户）
选择 
coalesce(max(排名=1时账户结束的情况),'-') account1,
coalesce(max(排名=2则账户结束的情况),'-') account2,
coalesce(max(排名=3时则账户结束的情况),'-') account3,
coalesce(max(排名=4时则账户结束的情况),'-') account4,
coalesce(max(排名=5时则账户结束的情况),'-') account5,
coalesce(max(排名=6则账户结束的情况),'-') account6
从 c
按 CNAME 分组

<前>帐户1 |帐户2 |帐户3 |帐户4 |帐户5 |帐号6
:----- | :----- | :----- | :----- | :----- | :--------
000001 | 000002 | 000003 | 000004 | - | -

db<>fiddle 此处

请参阅有关 bigQuery 中 group_concat 的更多信息，请参阅以下帖子：BigQuery GROUP_CONCAT 和 ORDER BY

You can use GROUP_CONCAT() to fetch a comma seperated list.
Or you can use RANK()to order your accounts in a CTEand then list them.
Attention : any 7th accounts or above will not be shown!

create table clients (
cname varchar(10),
account char(6));
insert into clients values
('Joe Shmo','000001'),
('Joe Shmo','000002'),
('Joe Shmo','000003'),
('Joe Shmo','000004');

select 
   cname "Name", 
   group_concat(account ) account_numbers
from clients
group by cname
order by cname;

Name     | account_numbers            
:------- | :--------------------------
Joe Shmo | 000001,000002,000003,000004

with c as 
(select
cname,
account,
rank() over (partition by cname order by account) ranking
from clients)
select 
coalesce(max(case when ranking=1 then account end),'   -') account1,
coalesce(max(case when ranking=2 then account end),'   -') account2,
coalesce(max(case when ranking=3 then account end),'   -') account3,
coalesce(max(case when ranking=4 then account end),'   -') account4,
coalesce(max(case when ranking=5 then account end),'   -') account5,
coalesce(max(case when ranking=6 then account end),'   -') account6
from c
group by cname

account1 | account2 | account3 | account4 | account5 | account6
:------- | :------- | :------- | :------- | :------- | :-------
000001   | 000002   | 000003   | 000004   |    -     |    -

db<>fiddle here

Please see the following post for more information on group_concat in bigQuery: BigQuery GROUP_CONCAT and ORDER BY

回复收藏 0 原文

~没有更多了~