declare @t table(a varchar(10),b varchar(10),c varchar(10),d int,id int) insert into @t select 'a1' ,'b1' ,'c1' ,100 ,1 union all select 'a1' ,'b1' ,'c1' ,-100 ,2 union all select 'a1' ,'b1' ,'c1' ,200 ,3 union all select 'a1' ,'b1' ,'c1' ,-300 ,4 union all select 'a2' ,'b2' ,'c2' ,100 ,5select * from @t a where not exists(select 1 from @t where a=a.a and b=a.b and c=a.c and d+a.d=0)
declare @ta table(a varchar(2), b varchar(2), c varchar(2), d int, id int) insert @ta select 'a1', 'b1', 'c1', 100, 1 insert @ta select 'a1', 'b1', 'c1', -100, 2 insert @ta select 'a1', 'b1', 'c1', 200, 3 insert @ta select 'a1', 'b1', 'c1', -300, 4 insert @ta select 'a2', 'b2', 'c2', 100, 5select * from @ta where id not in (select id from @ta a where exists (select 1 from @ta where a=a.a and b=a.b and c=a.c and ( (d!=a.d and d=abs(a.d)) or (a.d!=d and a.d=abs(d)) )) ) (1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响) a b c d id ---- ---- ---- ----------- ----------- a1 b1 c1 200 3 a1 b1 c1 -300 4 a2 b2 c2 100 5(3 行受影响)
insert into @t select 'a1' ,'b1' ,'c1' ,100 ,1
union all select 'a1' ,'b1' ,'c1' ,-100 ,2
union all select 'a1' ,'b1' ,'c1' ,200 ,3
union all select 'a1' ,'b1' ,'c1' ,-300 ,4
union all select 'a2' ,'b2' ,'c2' ,100 ,5select * from @t a where not exists(select 1 from @t where a=a.a and b=a.b and c=a.c and d+a.d=0)
insert @ta select 'a1', 'b1', 'c1', 100, 1
insert @ta select 'a1', 'b1', 'c1', -100, 2
insert @ta select 'a1', 'b1', 'c1', 200, 3
insert @ta select 'a1', 'b1', 'c1', -300, 4
insert @ta select 'a2', 'b2', 'c2', 100, 5select * from @ta
where id not in
(select id
from @ta a
where exists
(select 1 from @ta where a=a.a and b=a.b and c=a.c and
(
(d!=a.d and d=abs(a.d))
or
(a.d!=d and a.d=abs(d))
))
)
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
a b c d id
---- ---- ---- ----------- -----------
a1 b1 c1 200 3
a1 b1 c1 -300 4
a2 b2 c2 100 5(3 行受影响)