请教大牛一道SQL题

Question

求简单和高效的Query:Input table:
ID Number
1 2
2 1
3 6
4 3
5 5Input TotalNumber: 10How to find the latest ID with number sum is 10
Step1: Starting from ID 5 find number 5, 10-5=5
Step2: From ID 4 find number 3, 5-3=2
Step3: From ID 3 find number 6, since 6 >2 so no more ID searching.Output table should be:
ID  Number
3 2
4 3
5 5----------------
More complex case
Input table:
Category ID Number
1 1 2
1 2 1
1 3 6
1 4 3
1 5 5
2 1 2
2 2 6
2 3 2Input TotalNumber for category 1 is 10, for category 2 is 5Output table should be:
Category ID  Number
1 3 2
1 4 3
1 5 5
2 2 3
2 3 2

Answer 1

select 1 Category, 1 ID, 2 Number
into #temp
union all select 1, 2,1
union all select 1, 3,6
union all select 1, 4,3
union all select 1, 5,5
union all select 2, 1,2
union all select 2, 2,6
union all select 2, 3,2with t1 as
(
select Category,ID
,Number=case 
         when Category=1 
      then case when sumNumber<10 then Number
            else 10- (sumNumber-Number)
   end
         when Category=2
      then case when sumNumber<5 then Number
            else 5- (sumNumber-Number)
   end
end
from
(
select *,sum(Number) over(partition by category order by id desc) sumNumber
from #temp 
) t
) 
select *
from t1
where Number>0
order by Category,id

Answer 2

create  table #tb(id int,number int)
insert into #tb 
select 1,2 union all
select 2,1 union all
select 3,6 union all
select 4,3 union all
select 5,5 
select * from #tb ;with cte as 
( select top 1 id,case when number>=10 then 10 else number end as 'number' ,case when (10-number)<=0 then 0 else (10-number)end  as x  from #tb order by id desc
 union all
 select b.id,case when b.number>=a.x then a.x else b.number end ,case when (a.x -b.number)<=0 then 0 else (a.x-b.number ) end     from cte a join #tb b on b.id =a.id -1 
)
select id,number  from cte where number <>0
order by iddrop table #tb

Answer 3

-- table1
create table hg1
(ID int,Number int)insert into hg1
 select 1,2 union all
 select 2,1 union all
 select 3,6 union all
 select 4,3 union all
 select 5,5
-- test1
declare @TotalNumber int
select @TotalNumber=10;with t1 as
(select a.ID,a.Number,
        (select sum(b.Number) from hg1 b where b.ID>=a.ID) 'tn'
 from hg1 a),
t2 as
(select ID,case when tn<=@TotalNumber then Number
                else Number-(tn-@TotalNumber) end 'Number'
 from t1)
select ID,Number from t2 where Number>0/*
ID          Number
----------- -----------
3           2
4           3
5           5(3 row(s) affected)
*/-- table2
create table hg2
(Category int,ID int,Number int)insert into hg2
 select 1,1,2 union all
 select 1,2,1 union all
 select 1,3,6 union all
 select 1,4,3 union all
 select 1,5,5 union all
 select 2,1,2 union all
 select 2,2,6 union all
 select 2,3,2
-- test2
declare @TotalNumber1 int,@TotalNumber2 int
select @TotalNumber1=10,@TotalNumber2=5;with t1 as
(select a.Category,a.ID,a.Number,
        (select sum(b.Number) from hg2 b where b.Category=a.Category and b.ID>=a.ID) 'tn',
        case a.Category when 1 then @TotalNumber1 
                        when 2 then @TotalNumber2 end 'Total'
 from hg2 a),
t2 as
(select Category,ID,
        case when tn<=Total then Number
             else Number-(tn-Total) end 'Number'
 from t1)
select Category,ID,Number from t2 where Number>0/*
Category    ID          Number
----------- ----------- -----------
1           3           2
1           4           3
1           5           5
2           2           3
2           3           2(5 row(s) affected)
*/

Answer 4

谢谢大家的回复。如果有一百万个CategoryID,怎么写执行速度更快啊？

Answer 5

如3楼例子,将变量@TotalNumber1,@TotalNumber2..保存为表,然后关联查询.