代码之家 › 专栏 › 技术社区 › Salman Arshad

选择行,直到运行总和达到特定值

window-functions sql-server-2012 sql-server sql

Salman Arshad · 技术社区 · 6 年前

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

以及值,例如300或301(用户变量或列)。我要选择行,直到运行total of amount达到指定值,并执行以下操作:

对于300,我想为a选择前3行,为b选择前2行
对于301,我想为a选择前4行,为b选择前3行

这应该很简单,但我找到的解决方案不处理第二种情况。

2 回复 | 直到 6 年前

Salman Arshad 6 年前

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

DECLARE @Total INT = 301;

WITH cte AS
(
    SELECT *, SUM(amount) OVER (PARTITION BY usr ORDER BY dt) AS RunTotal
    FROM @t
)
SELECT *
FROM   cte
WHERE  cte.RunTotal - cte.amount < @Total -- running total for previous row is less
                                          -- than @Total then include current row

3N1GM4 6 年前

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

declare @target int = 300;

with cte_RunningTotal as
(
    select  
        usr,
        dt,
        amount,
        sum(amount) over (partition by usr order by dt rows unbounded preceding) as runningTotal
    from @t 
)
select *
from cte_RunningTotal 
where runningTotal < @target + amount
order by usr, dt

推荐文章