求SQL语句：根据总数查询出批次数量

[wxskysoft]

批次表：
批次ID 批次数量
11 0
22 3
38 1
77 9

如输入总数=10 则要求输出结果如下，优先选择批次ID小的。
批次ID 批次数量
22 3
38 1
77 6

如输入总数=20 则要求输出结果如下。
批次ID 批次数量
22 3
38 1
77 9
非常感谢！

[tiegenZ]

SQL:
select
C.id,
(Case when C.num2>=10 then C.num-(C.num2-10) else C.num end)as num
from(
select
A.id,
A.num,
(select Top(2) SUM(num) from dbo.test B where A.id>=B.id) as num2
from dbo.test A
where A.num > 0
)C

总数只需修改最外层case when中的数值即可
总数等于10效果图：
总数等于20效果图：

[wxskysoft]

好棒啊，就是有个BUG，当总数等于1时会出错

[tiegenZ]

引用 3 楼 wxskysoft 的回复:

好棒啊，就是有个BUG，当总数等于1时会出错

在外面再加个查询即可
select
*
from (
select
C.id,
(Case when C.num2>=1 then C.num-(C.num2-1) else C.num end)as num
from(
select
A.id,
A.num,
(select Top(2) SUM(num) from dbo.test B where A.id>=B.id) as num2
from dbo.test A
where A.num > 0
)C
)D where D.num > 0

效果图：

[wxskysoft]

就是我要的，非常感谢。

[繁花尽流年]

if not object_id(N'Tempdb..#tmp_1') is null
    drop table #tmp_1
Go
Create table #tmp_1([NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #tmp_1
select N'a',50 union all
select N'b',100 union all
select N'c',60 union all
select N'd',80 
Go
 
if not object_id(N'Tempdb..#tmp_2') is null
    drop table #tmp_2
Go
Create table #tmp_2([ID] int ,[NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #tmp_2
select 1,'a',50 union all
select 2,'a',100 union all
select 3,'b',20 union all
select 4,'b',20 union all
select 5,'b',90 union all
select 6,'c',100 union all
select 7,'d',20 union all
select 8,'d',50 
GO
 
--code
;WITH a AS (
    SELECT id,NAME,b.qty ,IIF(total<=0,qty,qty-total) used_qty,total
    ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY id DESC ) AS rno
    FROM (
        SELECT a.id,a.NAME,a.qty,SUM(a.qty) OVER(PARTITION BY a.NAME ORDER BY a.id)  AS total
        FROM (
            SELECT id,NAME,qty from #tmp_2
            UNION
            SELECT 0,NAME,qty*(-1) from #tmp_1
        ) a
    ) b
    WHERE b.qty>b.total
)
 
SELECT a.id,a.NAME,b.qty AS required_qty,a.qty,a.used_qty,IIF (a.rno=1 ,a.total,0) AS surplus_qty
FROM a
LEFT JOIN #tmp_1 b ON b.NAME=a.NAME
ORDER BY a.id

之前写的一个很类似的例子，LZ可以参考下