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

I have 3 distinct queries in a Stored Procedure in Sql Server. I need to merge the results grouping by "Date, Team, Account", and having the columns:

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

(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.

我的sql有点复杂，所以我不能在这里发布，以免太复杂，但我需要一些命令，比如Merge或Union all，甚至临时表，但我不知道在这种情况下如何使用。

Query 1

查询1

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

Query 2

查询2

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

Query 3

查询3

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

Query Result

查询结果

╔═══════════╦════════╦═══════════╦════════════╦══════════════╦══════════════╗
║   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 ║
╚═══════════╩════════╩═══════════╩════════════╩══════════════╩══════════════╝

2 个解决方案

#1

You can do this with either full outer join or with union all and group by. Here is the union all method:

您可以使用完整的外部连接或联合all和group by来实现这一点。这里是union all method:

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;

#2

Create table

创建表

DECLARE @q1 TABLE ([Date] DATE, TeamId INT, AccountId INT, TransactionQty INT, NumberUnits INT)
DECLARE @q2 TABLE ([Date] DATE, TeamId INT, AccountId INT, TransactionQty INT, NumberCartons INT, NumberUnits INT)
DECLARE @q3 TABLE ([Date] DATE, TeamId INT, AccountId INT, TotalPallets INT)

Sample data

样本数据

INSERT INTO @q1 VALUES ('8/12/2014', 4, 1989, 4, 4)
INSERT INTO @q2 VALUES ('8/12/2014', 4, 1989, 6, 6, 1)
INSERT INTO @q3 VALUES ('8/12/2014', 5, 2000, 2)
                       ,('9/12/2014', 4, 1989, 1)

Query

查询

SELECT  [Date], TeamId, AccountId,
        ISNULL(SUM(NumberUnits), 0) AS TotalUnits,
        ISNULL(SUM(NumberCartons), 0),
        ISNULL(SUM(TotalPallets), 0)
FROM (
    SELECT [Date], TeamId, AccountId, NULL AS NumberCartons, NumberUnits, NULL AS TotalPallets FROM @q1
    UNION ALL
    SELECT [Date], TeamId, AccountId, NumberCartons, NumberUnits, NULL FROM @q2
    UNION ALL
    SELECT [Date], TeamId, AccountId, NULL, NULL, TotalPallets FROM @q3
    ) AS t
GROUP BY [Date], TeamId, AccountId

#1