select t.* from (select b,(select top 1 d from t where b=a.b and d<>a.d) from t a group by b) tem,t where tem.b=t.b and tem.d=t.d
--测试环境 declare @ table(a int identity,b char(1),c int, d char(1)) --测试数据 insert @ select 'a', 1 ,'A' union all select 'a', 2 ,'A' union all select 'a', 3 ,'B' union all select 'b', 1 ,'C' union all select 'b', 2 ,'D' union all select 'b', 3 ,'D' union all select 'c', 1 ,'E' union all select 'c', 2 ,'F' --实现 select * from @ a where exists(select * from @ b where a.b = b.b and a.c = b.c - 1 and a.d <> b.d) /* 测试结果 a b c d ----------- ---- ----------- ---- 2 a 2 A 4 b 1 C 7 c 1 E(所影响的行数为 3 行) */
create table wystest1( a int, b char(1), c int, d char(1)) go insert into wystest1 select 1,'a',1,'a' insert into wystest1 select 2,'a',2,'a' insert into wystest1 select 3,'a',3,'b' insert into wystest1 select 4,'b',1,'c' insert into wystest1 select 5,'b',2,'d' insert into wystest1 select 6,'b',3,'d' insert into wystest1 select 7,'c',1,'e' insert into wystest1 select 8,'c',2,'f'goselect a.* from wystest1 a,wystest1 b where a.a=b.a-1 and a.b=b.b and a.d<>b.d
select * from t tem where (select top 1 d from t where b=tem.b and a>tem.a)<>tem.d
(select b,(select top 1 d from t where b=a.b and d<>a.d) from t a group by b) tem,t
where tem.b=t.b and tem.d=t.d
declare @ table(a int identity,b char(1),c int, d char(1))
--测试数据
insert @
select
'a', 1 ,'A'
union all select
'a', 2 ,'A'
union all select
'a', 3 ,'B'
union all select
'b', 1 ,'C'
union all select
'b', 2 ,'D'
union all select
'b', 3 ,'D'
union all select
'c', 1 ,'E'
union all select
'c', 2 ,'F'
--实现
select * from @ a
where exists(select * from @ b where a.b = b.b and a.c = b.c - 1 and a.d <> b.d)
/*
测试结果
a b c d
----------- ---- ----------- ----
2 a 2 A
4 b 1 C
7 c 1 E(所影响的行数为 3 行)
*/
a int,
b char(1),
c int,
d char(1))
go
insert into wystest1 select 1,'a',1,'a'
insert into wystest1 select 2,'a',2,'a'
insert into wystest1 select 3,'a',3,'b'
insert into wystest1 select 4,'b',1,'c'
insert into wystest1 select 5,'b',2,'d'
insert into wystest1 select 6,'b',3,'d'
insert into wystest1 select 7,'c',1,'e'
insert into wystest1 select 8,'c',2,'f'goselect a.* from wystest1 a,wystest1 b where a.a=b.a-1 and a.b=b.b and a.d<>b.d
where (select top 1 d from t where b=tem.b and a>tem.a)<>tem.d