declare @tb table(F1 int,F2 varchar(10)) insert @tb select 1,'A' union select 2,'B' union select 3,'B' union select 4,'C' union select 5,'C' union select 6,'C' select [F1]=(select count(1) from (select distinct F2 from @tb)A where F2<=t.F2), F2 from @tb t where not exists(select 1 from @tb where F2=t.F2 and F1>t.F1)--结果 /*F1 F2 ----------- ---------- 1 A 2 B 3 C(所影响的行数为 3 行) */
--生成测试数据 create table #T(F1 int,F2 char(1)) insert into #T select 1,'A' insert into #T select 2,'B' insert into #T select 3,'B' insert into #T select 4,'C' insert into #T select 5,'C' insert into #T select 6,'C' --执行查询语句 select a.* from #T a where not exists(select 1 from #T where F1=a.F1-1 and F2=a.F2) --输出查询结果 F1 F2 --- --- 1 A 2 B 4 C
declare @tb table(F1 int,F2 varchar(10)) insert @tb select 1,'A' union select 2,'B' union select 3,'B' union select 4,'C' union select 5,'C' union select 6,'C' select [F1]=(select count(1) from (select distinct F2 from @tb)A where F2<=t.F2), F2 from @tb t where not exists(select 1 from @tb where F2=t.F2 and F1>t.F1)--结果 /*F1 F2 ----------- ---------- 1 A 2 B 3 C(所影响的行数为 3 行) */
insert @tb
select 1,'A' union
select 2,'B' union
select 3,'B' union
select 4,'C' union
select 5,'C' union
select 6,'C'
select [F1]=(select count(1) from (select distinct F2 from @tb)A where F2<=t.F2),
F2
from @tb t
where not exists(select 1 from @tb where F2=t.F2 and F1>t.F1)--结果
/*F1 F2
----------- ----------
1 A
2 B
3 C(所影响的行数为 3 行)
*/
create table #T(F1 int,F2 char(1))
insert into #T select 1,'A'
insert into #T select 2,'B'
insert into #T select 3,'B'
insert into #T select 4,'C'
insert into #T select 5,'C'
insert into #T select 6,'C'
--执行查询语句
select
a.*
from
#T a
where
not exists(select 1 from #T where F1=a.F1-1 and F2=a.F2)
--输出查询结果
F1 F2
--- ---
1 A
2 B
4 C
insert @tb
select 1,'A' union
select 2,'B' union
select 3,'B' union
select 4,'C' union
select 5,'C' union
select 6,'C'
select [F1]=(select count(1) from (select distinct F2 from @tb)A where F2<=t.F2),
F2
from @tb t
where not exists(select 1 from @tb where F2=t.F2 and F1>t.F1)--结果
/*F1 F2
----------- ----------
1 A
2 B
3 C(所影响的行数为 3 行)
*/