Sql 获取满足给定汇总值的前N条记录

declare @ivt table(Qty int,Code nvarchar(10)) 2 insert into @ivt(Qty,Code) 3 select 7,‘a‘ union 4 select 3,‘b‘ union 5 select 4,‘c‘ 6 select * from @ivt order by code 7 8 declare @tb table(Id int ,Qty int,Code nvarchar(10)) 9 insert into @tb(Id,Qty,Code) 10 select 1, 1,‘a‘ union 11 select 2,2,‘b‘ union 12 select 3,3,‘c‘ union 13 select 4,2,‘a‘ union 14 select 5,1,‘b‘ union 15 select 6,3,‘a‘ union 16 select 7,1,‘c‘ union 17 select 8,3,‘a‘ union 18 select 9,2,‘c‘ union 19 select 10,4,‘a‘ 20 select * from @tb order by id 21 22 select a.Id,a.Qty,a.Code from @tb a join @tb b on b.id<=a.Id and a.Code=b.Code 23 join @ivt c on c.Code=a.Code 24 group by a.Id,a.Code,a.Qty,c.Qty 25 having sum(b.qty)<=c.Qty

 

https://dba.stackexchange.com/questions/74846/sql-select-top-x-rows-having-sum-limit

DECLARE @Limit FLOAT
SELECT @Limit = 30

SELECT
    o1.Id
    , o1.Cost
    , o1.Category
FROM
    MyOrders o1
    -- Join all orders with same category and inferior ID
    INNER JOIN MyOrders o2 ON o2.ID <= o1.ID AND o1.Category = o2.Category
GROUP BY
    o1.Id
    , o1.Cost
    , o1.Category
HAVING
    SUM(o2.Cost) < @Limit
ORDER BY
    ID

Sql 获取满足给定汇总值的前N条记录

标签：nbsp   sql   div   class   font   join   code   ack   mit