SQL Server：合并同一行中的两个值

Question

我有以下联合所有结果，

ID  Name     Date         Team         Total#1     Total#2
1   Test_1   4/25/2011    Team_1         110          0
1   Test_1   4/25/2011    Team_2         20           0
1   Test_1   4/25/2011    Team_3         170          0
1   Test_1   4/25/2011    Team_1         0            151
1   Test_1   4/25/2011    Team_3         0            98

我希望将每个团队的结果合并起来。有一些重复的球队名称具有不同的总价值，并且有一些独特的球队名称只有一个总价值。

ID  Name     Date         Team         Total#1     Total#2
1   Test_1   4/25/2011    Team_1         110          151
1   Test_1   4/25/2011    Team_2         20           0
1   Test_1   4/25/2011    Team_3         170          98

有什么想法吗？

Answer 1

SELECT Id, Name, Date, Team, SUM([Total#1]) [Total#1], SUM([Total#2]) [Total#2]
FROM YourUnionResult
GROUP BY Id, Name, Date, Team

Answer 2

只需使用聚合函数和GROUP BY：

SELECT ID, Name, Date, Team, SUM(Total#1) as Total1, Sum(Total#2) as Total2
FROM Mytable
WHERE ...
GROUP BY ID, Name, Date, Team

Answer 3

这行不通？

select id,name,date,team,sum(Total#1) as Total#1,sum(Total#2) as Total#2
from Your table
group by id,name,date,team

Answer 4

将 select... union select... 设为子查询，“外部”查询针对它发出一个 group by 。

Answer 5

假设由于某种原因标准分组不适合您可以这样做

SELECT t1.id, 
       t1.name, 
       t1.DATE, 
       t1.team, 
       t1.total_1, 
       t2.total_2 
FROM   (SELECT id, 
               name, 
               DATE, 
               team, 
               somecomplicatedcalculation total_1 
        FROM   your table) t1 
       LEFT JOIN (SELECT id, 
                         name, 
                         DATE, 
                         team, 
                         somecomplicatedcalculation total_2 
                  FROM   your table) t2 
         ON t1.id = t2.id
            and t1.team = t2.team

您也可以对 CTE 执行相同的操作

WITH t1 
     AS (SELECT id, 
                name, 
                DATE, 
                team, 
                somecomplicatedcalculation total_1 
         FROM   your table), 
     t2 
     AS (SELECT id, 
                name, 
                DATE, 
                team, 
                somecomplicatedcalculation total_2 
         FROM   your table) 
SELECT * 
FROM   t1 
       INNER JOIN t2 
         ON t1.id = t2.id 
            and t1.team = t2.team

或联合上的分组将起作用

SELECT id, 
       name, 
       DATE, 
       team, 
       SUM(total_1), 
       SUM(total_2) 
FROM   (SELECT id, 
               name, 
               DATE, 
               team, 
               somecomplicatedcalculation total_1, 
               0                          AS total_2 
        FROM   yourtable 
        UNION ALL 
        SELECT id, 
               name, 
               DATE, 
               team, 
               0                          AS total_1, 
               somecomplicatedcalculation total_2 
        FROM   yourtable) total 
GROUP BY id, 
       name, 
       DATE, 
       team