---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] varchar(1),[col2] int) insert [tb] select 'a',10 union all select 'b',20 union all select 'c',30
---查询--- declare @num int set @num=40 select col1, case when @num-(select sum(col2) from tb where col1<=t.col1)>=0 then col2 else @num-(select sum(col2) from tb where col1<t.col1) end as col2 from tb t---结果--- col1 col2 ---- ----------- a 10 b 20 c 10(3 行受影响)
declare @tb table (c char(1),n int); insert into @tb select 'a',10 union all select 'b',20 union all select 'c',30;declare @s int; set @s=40;with t1 as( select c,n, isnull((select SUM(n) from @tb where c<t.c),0) s from @tb t ) select c,(case when s+n<=@s then n else @s-s end) alloc from t1 where s<@s; /* a 10 b 20 c 10 */
--把树哥的稍微改下即可 declare @num int set @num=40 select col1, case when (select sum(col2) from tb where col1<=t.col1) <= @num then [col2] else case when isnull((select sum(col2) from tb where col1<t.col1),0)<=@num then @num- isnull((select sum(col2) from tb where col1<t.col1),0) else 0 end end as col2 from tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] int)
insert [tb]
select 'a',10 union all
select 'b',20 union all
select 'c',30
---查询---
declare @num int
set @num=40
select
col1,
case
when @num-(select sum(col2) from tb where col1<=t.col1)>=0 then col2
else @num-(select sum(col2) from tb where col1<t.col1)
end as col2
from tb t---结果---
col1 col2
---- -----------
a 10
b 20
c 10(3 行受影响)
declare @tb table (c char(1),n int);
insert into @tb
select 'a',10 union all select 'b',20 union all
select 'c',30;declare @s int;
set @s=40;with t1 as(
select c,n,
isnull((select SUM(n) from @tb where c<t.c),0) s
from @tb t
)
select c,(case when s+n<=@s then n else @s-s end) alloc from t1 where s<@s;
/*
a 10
b 20
c 10
*/
--把树哥的稍微改下即可
declare @num int
set @num=40
select
col1,
case
when (select sum(col2) from tb where col1<=t.col1) <= @num then [col2]
else case when isnull((select sum(col2) from tb where col1<t.col1),0)<=@num
then @num- isnull((select sum(col2) from tb where col1<t.col1),0)
else 0 end
end as col2
from tb t