難以在SELFJOIN(SQL)中生成不重復的不同行

我對 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;

不幸的是，結果是這樣的，我會得到一個如下所示的表格：

我知道對于兩個以上的帳戶，我需要兩個以上的聯接，但我還沒有弄清楚如何去做。

有沒有辦法防止重復輸入？

我正在使用 BigQuery 順便說一句。

uj5u.com熱心網友回復：

在第一步中，為每個客戶的帳戶編號。然后使用條件聚合來獲取每個帳戶一列。

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;

uj5u.com熱心網友回復：

您可以使用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)

uj5u.com熱心網友回復：

您可以使用它GROUP_CONCAT()來獲取逗號分隔的串列。
或者，您可以使用RANK()在 a 中對您的帳戶進行排序CTE，然后將它們列出。
注意：任何第7個或以上的帳戶將不會顯示！

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;

姓名 | 帳號            
:------- | :----------------------------
喬什莫 | 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

帳戶1 | 帳戶2 | 帳戶3 | 帳戶4 | 帳戶5 | 帳戶6
:------- | :------- | :------- | :------- | :------- | :--------
000001 | 000002 | 000003 | 000004 | - | -    

db<>在這里擺弄

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

標籤：sql 加入 谷歌大查询 自加入

上一篇：用where加入兩個表

下一篇：MySQL-計算2個最接近的記錄之間的平均時間差