将包含相同Id但不同数据的两行合并为一行 - Merge two rows that contain the same Id but different data into one row

我有一个非常棘手的问题要问你们

假设我有以下表格

<<p>表strong> AccountType :

Id       Type
 1     Checking
 2      Savings

<<p>表strong> Customer_Account :

Customer_Id      Account_Type_Id
    450                 1
    450                 2
    451                 1

表Customer_Account包含客户id及其帐户类型id的列表。Account_Type_Id是来自account_type . id的外键。

假设在Customer_Account表中，一个名为Josh (id 450)的客户可以同时拥有支票账户和储蓄账户，如上所示。通过在AccountType表上执行两次LEFT JOIN，我可以输出这个带有id和帐户类型的客户:

SELECT CustAccount.Customer_Id AS Id, Account1.Type AS Account_Type_1, Account2.Type AS Account_Type_2
FROM Customer_Account CustAccount
LEFT JOIN AccountType Account1
ON Account1.Id = CustAccount.Account_Type_Id
LEFT JOIN AccountType Account2
ON Account2.Id = CustAccount.Account_Type_Id

输出将是:

 Id        Account_Type_1         Account_Type_2           
450           Checking               Checking 
450           Savings                Savings
451           Checking               Checking

我要做的是，如果像Josh (id 450)这样的客户同时拥有支票账户和储蓄账户，我想将上面的两行数据输出到一行，如下所示:

 Id        Account_Type_1      Account_Type_2
450           Checking            Savings

而且，如果客户只有一种类型的帐户(如此处id为451的客户)，我希望在相应的列下只显示该类型的帐户，如下所示:

 Id        Account_Type_1      Account_Type_2
451           Checking

或者如果id为451的客户只有一个储蓄账户，输出应该是:

Id        Account_Type_1      Account_Type_2
451                              Savings

我希望'Checking'只出现在Accoun_Type_1和'Savings'下的Account_Type_2。如果我用GROUP BY CustAccount。Customer_Id，我得到这个:

 Id        Account_Type_1      Account_Type_2
450           Checking            Checking
451           Checking            Checking

任何专家的帮助将是非常感激的。

谢谢。

这看起来像一个直接的FULL OUTER JOIN应用程序:

SELECT COALESCE(ac1.id, ac2.id) AS id, ac1.Account_Type_1, ac2.Account_Type_2
  FROM (SELECT c.Customer_ID AS Id, t.Type AS Account_Type_1
          FROM Customer_Account AS c
          JOIN AccountType      AS t ON c.Account_Type_ID = t.ID AND t.ID = 1) AS ac1
  FULL OUTER JOIN
       (SELECT c.Customer_ID AS Id, t.Type AS Account_Type_2
          FROM Customer_Account AS c
          JOIN AccountType      AS t ON c.Account_Type_ID = t.ID AND t.ID = 2) AS ac2
    ON ac1.Id = ac2.Id;

如果您的DBMS不支持FULL OUTER JOIN，但支持LEFT OUTER JOIN，那么您可以使用:

SELECT ac0.id, ac1.Account_Type_1, ac2.Account_Type_2
  FROM (SELECT DISTINCT c.Customer_ID AS Id FROM Customer_Account AS c) AS ac0
  LEFT OUTER JOIN
       (SELECT c.Customer_ID AS Id, t.Type AS Account_Type_1
          FROM Customer_Account AS c
          JOIN AccountType      AS t ON c.Account_Type_ID = t.ID AND t.ID = 1) AS ac1
    ON ac0.id = ac1.id
  LEFT OUTER JOIN
       (SELECT c.Customer_ID AS Id, t.Type AS Account_Type_2
          FROM Customer_Account AS c
          JOIN AccountType      AS t ON c.Account_Type_ID = t.ID AND t.ID = 2) AS ac2
    ON ac0.Id = ac2.Id;

第一个子查询生成存在的客户id列表;第二步生成帐户类型1 (Checking)的列表;第三个生成帐户类型2 (Saving)的列表。连接确保每个帐户都被正确识别。

我想这会对你有帮助。

1: Sql Select Query Question。合并行或其他

为ON子句添加更多条件:

ON Account1.Id = CustAccount.Account_Type_Id and Account1.Account_Type_Id = 1
ON Account2.Id = CustAccount.Account_Type_Id and Account2.Account_Type_Id = 2

将产生包含客户持有的帐户的输出。如果他们只有一个帐户，那么另一个帐户类型将为NULL。

编辑:对不起，我没有意识到你没有一桌子的顾客。您可以创建一个包含不同Customer_Id值列表的临时表，并将其用作join中的第一个表。

select distinct Customer_Id into #Customers
    from Customer_Account

或者更直接的:

select distinct C.Customer_Id,
    ( select 'Checking' from Customer_Account where Customer_Id = C.CustomerId and Account_type_Id = 1 ) as Account_Type_1,
    ( select 'Savings' from Customer_Account where Customer_Id = C.CustomerId and Account_type_Id = 2 ) as Account_Type_2,
    from Customer_Account as C