group by和按列求和的SQL查询

Question

下面是我的表格：

ADate    | LFC | Ast  | Cost | Acnt | Rvn
2016-4-6 | AW  | AST1 | 20   | Ac1  | 100
2016-4-6 | AW  | AST2 | 30   | Ac1  | 100
2016-4-6 | AW  | AST1 | 20   | Ac2  | 200

下面是我的问题：

select 
    Adate
    ,LFC
    ,SUM(Cost) as TOTALCOST
    ,SUM(Rvn) as TOTALRVN 
from @Temp_Tab 
group by
    Adate
    ,LFC
;

下面是输出：

Adate      | LFC | TOTALCOST | TOTALRVN
2016-04-06 | AW  | 70        | 400

我想要以下输出，其中Cost的SUM将基于唯一的Ast，而Rvn的SUM将基于唯一的Acnt。

Adate      | LFC | TOTALCOST | TOTALRVN
2016-04-06 | AW  | 50        | 300

Answer 1

这里有一个可能的变体。只需根据需要多次GROUP数据即可。我使用常用表表达式(CTE)来使查询更容易阅读和理解。

示例数据

DECLARE @Temp_Tab TABLE ([ADate] datetime, [LFC] varchar(50), [Ast] varchar(50), [Cost] int, [Acnt] varchar(50), [Rvn] int);

INSERT INTO @Temp_Tab([ADate], [LFC], [Ast], [Cost], [Acnt], [Rvn]) VALUES
('2016-04-06 00:00:00', 'AW', 'AST1', 20, 'Ac1', 100),
('2016-04-06 00:00:00', 'AW', 'AST2', 30, 'Ac1', 100),
('2016-04-06 00:00:00', 'AW', 'AST1', 20, 'Ac2', 200);

查询

WITH
CTE_AstCosts
AS
(
    SELECT
        Adate
        ,LFC
        ,Ast
        ,MIN(Cost) AS CostPerAst
    FROM @Temp_Tab AS T
    GROUP BY Adate, LFC, Ast
)
,CTE_AstGroups
AS
(
    SELECT
        Adate
        ,LFC
        ,SUM(CostPerAst) AS CostPerGroup
    FROM CTE_AstCosts
    GROUP BY Adate, LFC
)
,CTE_AcntRvn
AS
(
    SELECT
        Adate
        ,LFC
        ,Acnt
        ,MIN(Rvn) AS RvnPerAcnt
    FROM @Temp_Tab AS T
    GROUP BY Adate, LFC, Acnt
)
,CTE_AcntGroups
AS
(
    SELECT
        Adate
        ,LFC
        ,SUM(RvnPerAcnt) AS RvnPerGroup
    FROM CTE_AcntRvn
    GROUP BY Adate, LFC
)
,CTE_Groups
AS
(
    SELECT
        Adate
        ,LFC
    FROM @Temp_Tab AS T
    GROUP BY Adate, LFC
)
SELECT
    CTE_Groups.Adate
    ,CTE_Groups.LFC
    ,CTE_AstGroups.CostPerGroup
    ,CTE_AcntGroups.RvnPerGroup
FROM
    CTE_Groups
    INNER JOIN CTE_AstGroups
        ON  CTE_AstGroups.Adate = CTE_Groups.Adate 
        AND CTE_AstGroups.LFC = CTE_Groups.LFC
    INNER JOIN CTE_AcntGroups
        ON  CTE_AcntGroups.Adate = CTE_Groups.Adate 
        AND CTE_AcntGroups.LFC = CTE_Groups.LFC
;

结果

+-------------------------+-----+--------------+-------------+
|          Adate          | LFC | CostPerGroup | RvnPerGroup |
+-------------------------+-----+--------------+-------------+
| 2016-04-06 00:00:00.000 | AW  |           50 |         300 |
+-------------------------+-----+--------------+-------------+