select rtype='a',rqty=2
union all
select rtype=null,rqty=5
union all
select rtype=null,rqty=6
union all
select rtype='b',rqty=1
union all
select rtype=null,rqty=3
union all
select rtype=null,rqty=5
union all
select rtype=null,rqty=6
union all
select rtype='b',rqty=1
union all
select rtype=null,rqty=3
(select count(*) from
(select rtype='a',rqty=2
union all
select rtype='a',rqty=5
union all
select rtype='a',rqty=6
union all
select rtype='b',rqty=1
union all
select rtype='b',rqty=3) b where rtype=a.rtype and rqty<a.rqty)>0 then null else rtype end,rqty
from
(select rtype='a',rqty=2
union all
select rtype='a',rqty=5
union all
select rtype='a',rqty=6
union all
select rtype='b',rqty=1
union all
select rtype='b',rqty=3) a
/*
结果
rtype rqty
--------------------
a 2
NULL 5
NULL 6
b 1
NULL 3*/
--建测试数据
create table #t(a char(1),b int)
insert #t
select 'a',2
union all
select 'a',5
union all
select 'a',6
union all
select 'b',1
union all
select 'b',3
union all
select 'b',3alter table #t add id int identity(1,1)
go
update #t set a=null where id not in (select id from (select min(id) id,a,min(b) b from #t group by a) a)
alter table #t drop column id
go
select * from #t--结果
a b
---- -----------
a 2
NULL 5
NULL 6
b 1
NULL 3
NULL 3(所影响的行数为 6 行)
--删除测试数据
drop table #t
go
select case when iid is null then rtype else null end as rtype,rqty from(Select (Select sum(1) from
(select rtype='a',rqty=2
union all
select rtype='a',rqty=5
union all
select rtype='a',rqty=6
union all
select rtype='b',rqty=1
union all
select rtype='b',rqty=3 ) a
where rtype <= b.rtype and rqty<b.rqty) as iid,rtype,rqty
from
(select rtype='a',rqty=2
union all
select rtype='a',rqty=5
union all
select rtype='a',rqty=6
union all
select rtype='b',rqty=1
union all
select rtype='b',rqty=3 ) b ) c