如何在公共表表达式中按两个数据字段进行分组

Question

我需要比较按 ID 分组的不同客户的两个表的最终余额。这些表具有相同的 ID，但一个表有多个 ID 条目，而另一个表则没有。我需要将表中的行与多个条目相加，因此我只有一个最终数字可以进行计算。

我的查询执行了这么多操作，但现在我还需要将其按月分组。我当前的查询对所有日期进行了总计。数据实际上需要按 ID 和月份排序，以便他们知道每个 ID 的月末余额。有没有办法用我目前所拥有的来做到这一点？

提前致谢。

这是我到目前为止所拥有的。

    -- Data setup
    CREATE TABLE [Table1]
    (
        [ID] INT,
        [cost] INT,
        [traceNumber] INT,
        [TheDate] DATE
    )

    INSERT [Table1]
    VALUES  (1, 200, 1001, '9/07/2011'),
            (1, -20, 1002, '9/08/2011'),
            (1, 130, 1003, '10/10/2011'),
            (2, 300, 1005, '10/10/2011')

    CREATE TABLE [Table2]
    (
        [ID] INT,
        [cost] INT
     )

     INSERT [Table2]
    VALUES  (1, 200),
            (2, 300)

    -- Query
    ;WITH [cteTable1Sum] AS
    (
        SELECT [ID], SUM([cost]) AS [cost]
        FROM [Table1]
        GROUP BY [ID]
    )       
    SELECT  [Table1].[ID], 
            [Table1].[TheDate], 
            [Table1].[traceNumber],
            [Table1].[cost] AS [Frost_Balance],
            cte.[cost] AS [SUM_Frost_Balance],
            [Table2].[cost] AS [Ternean_Balance],
            cte.[cost] - [Table2].[cost] AS [Ending_Balance]
    FROM [Table1]
    INNER JOIN [Table2]
        ON [Table1].[ID] = [Table2].[ID]
    INNER JOIN [cteTable1Sum] cte
         ON [Table1].[ID] = cte.[ID]

我尝试了这个，但得到了错误的答案。

    WITH [cteTable1Sum] AS 
         ( SELECT [ID], SUM([cost]) 
         AS [cost] FROM [Table1] 
         GROUP BY [ID], [TheDate] )

Answer 1

为此，您可能应该使用 MONTH() 和 YEAR() 函数：

;WITH [cteTable1Sum] AS
    (
        SELECT [ID],
              MONTH(TheDate)  as Mo,
              YEAR(TheDate) as yr,
              SUM([cost]) AS [cost]
        FROM [Table1]
        GROUP BY [ID], MONTH(TheDate), YEAR(TheDate)
    )       

这将为您提供每个 ID 的每月细分。然后将您的 JOIN 更改为：

INNER JOIN [cteTable1Sum] cte
         ON [Table1].[ID] = cte.[ID]
         AND MONTH(Table1.TheDate) = cte.Mo
         AND YEAR(Table1.TheDate) = cte.Yr