表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