时间戳

首页 > 解决方案 > 组合分组的行

sql - 组合分组的行

问题描述

   select *
     into #HoldingTable
     FROM
(Select A,
        B,
    sum(cast (C as money)) as C,
    count(D) as D
        FROM Table1 T1
        WHERE (stuff)
                group by A,B

union all
   Select A,
          B,
   sum(cast (C as money)) as C, 
   count(D) as D
        FROM Table2 T2
        WHERE (stuff)
              group by A,B
) as abc

如果 T1 和 T2 具有相同 A、B 的结果，则在获得结果时，它会将其列在 2 个不同的行中

select * from HoldingTable

aaa,bbb,10,4
aaa,bbb,5,3

希望它是

aaa,bbb,15,7

有没有办法做到这一点？

标签： sqltsqlgrouping

解决方案

这更易于阅读，并且应该与您尝试做的事情相同。

SELECT A, B, SUM(C) AS C, Count(D) AS D
INTO #HoldingTable
FROM (
    SELECT A, B, cast(C AS MONEY) AS C, D
    FROM Table1 T1
    WHERE (stuff)
    
    UNION ALL
    
    SELECT A, B, cast(C AS MONEY) AS C, D
    FROM Table1 T1
    WHERE (stuff)
    ) AS abc
GROUP BY A, B

您可以在子查询和外部查询中进行聚合，如下所示，但这只会使 IMO 更难阅读。

SELECT A, B, Sum(C) AS C, Sum(D) AS D
INTO #HoldingTable
FROM (
    SELECT A, B, sum(cast(C AS MONEY)) AS C, count(D) AS D
    FROM Table1 T1
    WHERE (stuff)
    GROUP BY A, B
    
    UNION ALL
    
    SELECT A, B, sum(cast(C AS MONEY)) AS C, count(D) AS D
    FROM Table2 T2
    WHERE (stuff)
    GROUP BY A, B
    ) AS abc
GROUP BY A, B

推荐阅读