sql - 如何通过相同的分组但不同的列(Sql Server 存储过程)合并 3 个不同的 Sql

Question

我在 Sql Server 的存储过程中有 3 个不同的查询。我需要合并结果分组“日期、团队、帐户”，并具有以下列:

(Query1.NumberUnits + Query2.NumberUnits) AS TotalUnits,
(Query2.NumberCartons) AS TotalCartons,
(Query3.TotalPallets) AS TotalPallets

My Sqls are a bit complex so I couldn't post here to don't make it too complicated, but I need some command like Merge or Union all or even temporary tables, but I don't know how to use in this case.

查询 1

╔═══════════╦════════╦═══════════╦════════════════╦═════════════╗
║   Date    ║ TeamId ║ AccountId ║ TransactionQty ║ NumberUnits ║
╠═══════════╬════════╬═══════════╬════════════════╬═════════════╣
║ 8/12/2014 ║      4 ║      1989 ║              4 ║           4 ║
╚═══════════╩════════╩═══════════╩════════════════╩═════════════╝

查询 2

╔═══════════╦════════╦═══════════╦════════════════╦═══════════════╦═════════════╗
║   Date    ║ TeamId ║ AccountId ║ TransactionQty ║ NumberCartons ║ NumberUnits ║
╠═══════════╬════════╬═══════════╬════════════════╬═══════════════╬═════════════╣
║ 8/12/2014 ║      4 ║      1989 ║              6 ║             6 ║           1 ║
╚═══════════╩════════╩═══════════╩════════════════╩═══════════════╩═════════════╝

查询 3

╔═══════════╦════════╦═══════════╦══════════════╗
║   Date    ║ TeamId ║ AccountId ║ TotalPallets ║
╠═══════════╬════════╬═══════════╬══════════════╣
║ 8/12/2014 ║      5 ║      2000 ║            2 ║
║ 9/12/2014 ║      4 ║      1989 ║            1 ║
╚═══════════╩════════╩═══════════╩══════════════╝

查询结果

╔═══════════╦════════╦═══════════╦════════════╦══════════════╦══════════════╗
║   Date    ║ TeamId ║ AccountId ║ TotalUnits ║ TotalCartons ║ TotalPallets ║
╠═══════════╬════════╬═══════════╬════════════╬══════════════╬══════════════╣
║ 8/12/2014 ║      4 ║      1989 ║          5 ║            6 ║            0 ║
║ 8/12/2014 ║      5 ║      2000 ║          0 ║            0 ║            2 ║
║ 9/12/2014 ║      4 ║      1989 ║          0 ║            0 ║            1 ║
╚═══════════╩════════╩═══════════╩════════════╩══════════════╩══════════════╝

Answer 1

您可以使用 full outer join 或使用 union all 和 group by 来完成此操作。这是 union all 方法:

with q1 as (<query1>),
     q2 as (<query2>),
     q3 as (<query3>)
select date, TeamId, AccountId,
       sum(NumberUnits) as TotalUnits,
       sum(NumberCartons) as TotalCartons,
       sum(TotalPallets) as TotalPallets
from ((select date, TeamId, AccountId, NumberUnits, 0 as NumberCartons, 0 as TotalPallets
       from q1 
      ) union all
      (select date, TeamId, AccountId, NumberUnits, NumberCartons, 0 as TotalPallets
       from q2 
      ) union all
      (select date, TeamId, AccountId, 0 as NumberUnits, 0 as NumberCartons, TotalPallets
       from q3 
      )
     ) qqq
group by date, TeamId, AccountId
order by date, TeamId, AccountId;