按等于特定值的特定列的总和分组行

小编典典

按等于特定值的特定列的总和分组行

sql

无论如何是按某个特定值对数据进行分组。例如，一个表中有Amount列，我想按每100个量对行进行分组，而无需while循环/光标:)

DueAmount = 100

收据表结构：

ReceiptNo   Amount
------------------
R1          100
R2          100
R3          70
R4          50
R5          200

输出：（ 将收据表按100分组）

DueNo/GroupKey      ReceiptNo   Amount
--------------------------------------
D1                  R1          100
D2                  R2          100
D3                  R3          70
D3                  R4          30
D4                  R4          20
D4                  R5          80
D5                  R5          100
D6                  R5          20

阅读 203

2021-05-30

共1个答案

小编典典

使用定义了“ Due”边界的助手表并计算出运行总计，可以计算出每个运行总量与“ Due”边界的交集：

With Receipt As ( --< Sample source table
    Select * From (Values
        ('R1', 100),
        ('R2', 100),
        ('R3',  70),
        ('R4',  50),
        ('R5', 200)
    ) V (ReceiptNo, Amount)
), ReceiptWithTotal As ( --< Source table with Running Totals calculated
    Select *,
        SUM(Amount) Over (Order By ReceiptNo Rows Unbounded Preceding) - Amount As RunningTotalBefore,
        SUM(Amount) Over (Order By ReceiptNo Rows Unbounded Preceding) As RunningTotalAfter
    From Receipt
), Due As ( --< Helper table to define intervals (can be generated dynamically to cover any Total)
    Select * From (Values
        ('D1',   0, 100),
        ('D2', 100, 200),
        ('D3', 200, 300),
        ('D4', 300, 400),
        ('D5', 400, 500),
        ('D6', 500, 600)
    ) V (DueNo, AmountLow, AmountHigh)
)
Select DueNo, ReceiptNo,
    IIF(AmountHigh < RunningTotalAfter, AmountHigh, RunningTotalAfter) -
    IIF(AmountLow > RunningTotalBefore, AmountLow, RunningTotalBefore) As Amount
From Due
Inner Join ReceiptWithTotal On NOT (RunningTotalAfter <= AmountLow OR RunningTotalBefore >= AmountHigh)

请注意：SUM(...) Over (Order By ... Rows Unbounded Preceding)并且IIF(...)仅在SQL
Server 2012+上可用。可以通过子查询在SQL Server 2008上执行相同的操作，尽管效率要低得多：

With Receipt As ( --< Sample source table
    Select * From (Values
        ('R1', 100),
        ('R2', 100),
        ('R3',  70),
        ('R4',  50),
        ('R5', 200)
    ) V (ReceiptNo, Amount)
), ReceiptWithTotal As ( --< Source table with Running Totals calculated
    Select *, RunningTotalAfter - Amount As RunningTotalBefore
    From (
        Select *,
            (Select SUM(Amount) From Receipt B Where B.ReceiptNo <= A.ReceiptNo) As RunningTotalAfter
        From Receipt A
    ) A
), Due As ( --< Helper table to define intervals (can be generated dynamically to cover any Total)
    Select * From (Values
        ('D1',   0, 100),
        ('D2', 100, 200),
        ('D3', 200, 300),
        ('D4', 300, 400),
        ('D5', 400, 500),
        ('D6', 500, 600)
    ) V (DueNo, AmountLow, AmountHigh)
)
Select DueNo, ReceiptNo,
    CASE WHEN AmountHigh < RunningTotalAfter THEN AmountHigh ELSE RunningTotalAfter END -
    CASE WHEN AmountLow > RunningTotalBefore THEN AmountLow ELSE RunningTotalBefore END As Amount
From Due
Inner Join ReceiptWithTotal On NOT (RunningTotalAfter <= AmountLow OR RunningTotalBefore >= AmountHigh)

2021-05-30