[求购SQL 语句一条]解决方案

[求购SQL 语句一条]

SQL code

create table #tmp(  Id int,  BatchNo varchar(10),  Num int )insert into #tmp select 1, 'B0001',10insert into #tmp select 2, 'B0002',10insert into #tmp select 5, 'B0003',30insert into #tmp select 9, 'B0004',40insert into #tmp select 10,'B0005',20*********--问题，查询出满足出库数量的批次，比我如出库数量是50， 那么就返回批次Id    1,2,5--如果出库数量是30，返回批次Id   1,2,5

SQL code

create table #tmp(  Id int,  BatchNo varchar(10),  Num int )insert into #tmp select 1, 'B0001',10insert into #tmp select 2, 'B0002',10insert into #tmp select 5, 'B0003',30insert into #tmp select 9, 'B0004',40insert into #tmp select 10,'B0005',20--30select    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--结果IDs1,2,5--50select    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--结果IDs1,2,5
[解决办法]
SQL code
create table t1(    id int,    batchno varchar(10),    num int)insert into t1 select 1, 'B0001',10insert into t1 select 2, 'B0002',10insert into t1 select 5, 'B0003',30insert into t1 select 9, 'B0004',40insert into t1 select 10,'B0005',20select * from t1declare @num intset @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)-------------------------------id125
[解决办法]
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
[解决办法]
楼上正解