表X 字段：a b c d id 内容：a1 b1 c1 100 1 a1 b1 c1 -100 2 a1 b1 c1 200 3 a1 b1 c1 -300 4 a2 b2 c2 100 5 问题：查找出不符合这些条件的记录[a相等，b相等,c相等,|d|相等（但d肯定一个正，一个负）]，并显示id 需要得到的结果：a b c d id a1 b1 c1 200 3 a1 b1 c1 -300 4 a2 b2 c2 100 5 这样的SQL语句怎么写，请各位指教，谢谢 ------解决方案1-------------------- 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 ,5 select * 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) ------解决方案2-------------------- 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, 5 select * 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)) )) )
引用：http://www.myexceptions.net/sql-server/142490.html