---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([type] varchar(1),[count] int) insert [tb] select 'a',9 union all select 'a',8 union all select 'a',7 union all select 'a',6 union all select 'b',10 union all select 'b',9 union all select 'b',8 union all select 'b',7 union all select 'b',6
---查询--- select * from tb t where (select count(1) from tb where [type]=t.[type] and [count]>t.[count])<2 ---结果--- type count ---- ----------- a 9 a 8 b 10 b 9(所影响的行数为 4 行)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([type] varchar(1),[count] int) insert [tb] select 'a',9 union all select 'a',8 union all select 'a',7 union all select 'a',6 union all select 'b',10 union all select 'b',9 union all select 'b',8 union all select 'b',7 union all select 'b',6 select a.* from tb a ,tb b where a.type = b.type group by a.type,a.count having sum(case when a.count < b.count then 1 else 0 end) < 2type count ---- ----------- a 8 a 9 b 9 b 10
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([type] varchar(1),[count] int)
insert [tb]
select 'a',9 union all
select 'a',8 union all
select 'a',7 union all
select 'a',6 union all
select 'b',10 union all
select 'b',9 union all
select 'b',8 union all
select 'b',7 union all
select 'b',6
---查询---
select
*
from tb t
where (select count(1) from tb where [type]=t.[type] and [count]>t.[count])<2
---结果---
type count
---- -----------
a 9
a 8
b 10
b 9(所影响的行数为 4 行)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([type] varchar(1),[count] int)
insert [tb]
select 'a',9 union all
select 'a',8 union all
select 'a',7 union all
select 'a',6 union all
select 'b',10 union all
select 'b',9 union all
select 'b',8 union all
select 'b',7 union all
select 'b',6
select a.*
from tb a ,tb b
where a.type = b.type
group by a.type,a.count
having sum(case when a.count < b.count then 1 else 0 end) < 2type count
---- -----------
a 8
a 9
b 9
b 10