create table #tmp
(
  Id int,
  BatchNo varchar(10),
  Num int 
)
insert into #tmp select 1, 'B0001',10
insert into #tmp select 2, 'B0002',10
insert into #tmp select 5, 'B0003',30
insert into #tmp select 9, 'B0004',40
insert into #tmp select 10,'B0005',20
***
***
***

--问题，查询出满足出库数量的批次，比我如出库数量是50， 那么就返回批次Id    1,2,5
--如果出库数量是30，返回批次Id   1,2,5

create table #tmp
(
  Id int,
  BatchNo varchar(10),
  Num int 
)
insert into #tmp select 1, 'B0001',10
insert into #tmp select 2, 'B0002',10
insert into #tmp select 5, 'B0003',30
insert into #tmp select 9, 'B0004',40
insert into #tmp select 10,'B0005',20

--30
select    stuff(( select ',' + CAST(ID AS VARCHAR(10)) 
                from #tmp AS B WHERE B.Id NOT IN (SELECT ID
                                                  FROM #tmp AS A
                                                  WHERE 30 <= (SELECT SUM(Num) FROM #tmp  AS B WHERE A.Id > B.Id)) for xml path('')),1,1,'') as IDs

--结果
IDs
1,2,5

--50
select    stuff(( select ',' + CAST(ID AS VARCHAR(10)) 
                from #tmp AS B WHERE B.Id NOT IN (SELECT ID
                                                  FROM #tmp AS A
                                                  WHERE 50 <= (SELECT SUM(Num) FROM #tmp  AS B WHERE A.Id > B.Id)) for xml path('')),1,1,'') as IDs

--结果
IDs
1,2,5

------解决方案--------------------
SQL code

create table t1
(
    id int,
    batchno varchar(10),
    num int
)
insert into t1 select 1, 'B0001',10
insert into t1 select 2, 'B0002',10
insert into t1 select 5, 'B0003',30
insert into t1 select 9, 'B0004',40
insert into t1 select 10,'B0005',20
select * from t1

declare @num int
set @num=30
;with aaa as
(
select ROW_NUMBER() over(order by id) as rowindex,a.*,
(select SUM(num)-@num from t1 where t1.id<=a.id) as num1 from t1 as a
)
select id from aaa where rowindex<=
(select top 1 rowindex from aaa where num1>=0 order by num1)

-------------------------------
id
1
2
5

------解决方案--------------------
SQL code
with tb as(
select *,rank()over(order by id) as row,num1=(select (isnull(sum(num),0)) as num from #tmp where id<a.id),num2=(select (sum(num)) as num from #tmp where id<=a.id) from #tmp a

)
select id=stuff((select ','+cast(id as varchar(10)) from tb where row<=a.row for xml path('')),1,1,'') from tb a where num1<30 and num2>=30

------解决方案--------------------
楼上正解