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,2 with 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
Posted by Colin at December 10, 2016 - 8:18 AM
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 id drop table #tb
Posted by Ted at December 12, 2016 - 8:33 AM
-- 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) */
Posted by Tony at December 16, 2016 - 9:06 AM
A simple and efficient it may not be possible. You belong to the aggregate value of screening, if you can write simple sentence, but the aggregation of multiple, if efficient can establish multilayer aggregate cache, but the statement complex.
Posted by Amanda at December 24, 2016 - 10:04 AM
Thank you reply.
If there are one million CategoryID, how to write faster execution.?
If there are one million CategoryID, how to write faster execution.?
Posted by Myron at December 27, 2016 - 10:08 AM
Such as floor 3 example, the variable @TotalNumber1, @TotalNumber2.. Saved as tables, and query results.
Posted by Tony at January 02, 2017 - 11:06 AM
Input table:
ID Number
1 2
2 1
3 6
4 3
5 5
Input TotalNumber: 10
How 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 2
Input TotalNumber for category 1 is 10, for category 2 is 5
Output table should be:
Category ID Number
1 3 2
1 4 3
1 5 5
2 2 3
2 3 2
Started by Myron at November 28, 2016 - 7:52 AM