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