select * from table1 where (b<>1 or c<>1 or d<>1 or e<>1)
select * from tb where cast(B as varchar)+cast(C as varchar)+cast(D as varchar)+cast(E as varchar) <>'1111'
select * from table1 where not(B=1 and C=1 and D=1 and E=1)--或者 select * from table1 where rtrim(B)+rtrim(C)+rtrim(D)+rtrim(E)='1111'
select * from table1 where A not in(select A from table1 where b=c and b=d and b=e and b=1)
没什么好办法,只能一个个的判断拼成字符串再比较也可能出现不正确的时候,比如: b = 1 c = 1 d = '' e = 11
select * from test where A not in (select A from test a where B=C and C=D and D=E )
select * from table1 where (b<>1 or c<>1 or d<>1 or e<>1)是对的,不应该这么简单吧,版主是不是有什么其它的用途
select * from table1 where (b<>c or b<>d or b<>e)
不好意思 ,让大家误解了补充以下内容:原题意思是相同的B(同组也就是一组记录中),该组中任何一条记录的C或D或E是相同的. 例如: A(pk) B C D E 1 b 1 2 3 (正常) 2 b 1 2 3 (正常) 3 b 1 2 4 (异常) 4 b 1 5 6 (异常) 5 b 1 2 3 (正常) 查寻结果为:3 b 1 2 4 4 b 1 5 6
select * from tb a where exists (select 1 from tb where A=a.A and B=a.B and (C<>a.C or D<>a.D or E<>a.E) )
select * from tb a where not exists (select 1 from ( select B,C,D,E from tb group by B,C,D,E having count(*)>1) b where b.b=a.b and b.c=a.c and b.d=a.d and b.e=a.e)
<>'1111'
select * from table1 where rtrim(B)+rtrim(C)+rtrim(D)+rtrim(E)='1111'
b = 1
c = 1
d = ''
e = 11
(select A from test a
where B=C and C=D and D=E
)
例如:
A(pk) B C D E
1 b 1 2 3 (正常)
2 b 1 2 3 (正常)
3 b 1 2 4 (异常)
4 b 1 5 6 (异常)
5 b 1 2 3 (正常)
查寻结果为:3 b 1 2 4
4 b 1 5 6
where exists (select 1 from tb where A=a.A and B=a.B
and (C<>a.C or D<>a.D or E<>a.E)
)
(select 1 from (
select B,C,D,E from tb group by B,C,D,E having count(*)>1) b
where b.b=a.b and b.c=a.c and b.d=a.d and b.e=a.e)