在视图的结果集中返回具有非聚合列的聚合列的最佳方法是什么

Question

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

表 1：

ID,    cost,    traceNumber,   TheDate
1,     200,     1001,          10/07/2011
1,     -20,     1002,          10/08/2011
1,     130,     1003,          10/10/2011
2,     300,     1005,          10/10/2011

表 2：

ID,    cost
1,     200
2,     300

ID 1 的结果为 310，与表 2 的 200 相比，相差 110

查询将如下所示。

SELECT DISTINCT
    Table1.ID,
    Table1.TheDate ,
    Table1.traceNumber,
    Table1.[cost] AS Frost_Balance, 
    SUM(Table1.[cost]) AS SUM_Frost_Balance, 
    Table2.[cost] AS Ternean_Balance, 
    SUM(Table1.[cost]) - Table2.[cost] AS Ending_Balance, 
FROM  
    Table1 
    INNER JOIN Table2 ON Table1.ID =Table2.CustomerID 
GROUP BY 
    dbo.Frost.ID

查询必须在结果集中显示多个列，因为它将是用于报告。我尝试按结果集中的所有列进行分组，但这给了我错误的结果。还有另一种方法来计算需要求和的列吗？

Answer 1

您可以使用“窗口聚合函数”，如下所示：

select 
    table1.*,
    sum(table1.cost) over (partition by table1.id) sum_frost_balance,
    table2.cost,
    sum(table1.cost) over (partition by table1.id) - table2.cost ending_balance
from table1
    join table2 on table1.id = table2.id

Answer 2

这就是你想做的吗？

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

INSERT [Table1]
VALUES  (1, 200, 1001, '10/07/2011'),
        (1, -20, 1002, '10/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]