“注意:每个表中都有两个字段X Y Z才能唯一标识这条记录。”--sorry,此处更正:两个字段-》三个字段
select a.* from a where not exists(select 1 from b where a.x=b.x and a.y=b.y and a.z=b.z) and not exists(select 1 from C where a.x=C.x and a.y=C.y and a.z=C.z) and not exists(select 1 from D where a.x=D.x and a.y=D.y and a.z=D.z)
--2005select x,y,z from a except select x,y,z from b except select x,y,z from c except select x,y,z from d
select x,y,z from a where not exists (select 1 from( select x,y,z from b union select x,y,z from c union select x,y,z from d )t where x = a.x and y = a.y and z = a.z )
select * from tbA a where not exists(select 1 from tbB where x=a.x and y=a.y and z=a.z) and not exists(select 1 from tbC where x=a.x and y=a.y and z=a.z) and not exists(select 1 from tbD where x=a.x and y=a.y and z=a.z)
select * from A except (select * from B union select * from C union select * from D)
select * from tablea a where not exists (select 1 from tableb where x=a.x and y=a.y and z=a.z) and not exists (select 1 from tablec where x=a.x and y=a.y and z=a.z) and not exists (select 1 from tabled where x=a.x and y=a.y and z=a.z)
问题是怎么我用NUSPHERE中的phpmyadmin2.2.1下用的mysql 3.23.44 版的 输入下面语句就报错呢?错误 SQL 语句 : SELECT * FROM `A` where X not in (SELECT X FROM `B`) LIMIT 0, 30MySQL 返回: You have an error in your SQL syntax near 'SELECT X FROM `B`) LIMIT 0, 30' at line 2
你用的是MYSQL,不是MSSQL,去问问MYSQL版块!
select A.X,A.Y,A.Z from A left join B on A.X=B.X and A.Y=B.Y and A.Z=B.Z left join C on A.X=C.X and A.Y=C.Y and A.Z=C.Z left join D on A.X=D.X and A.Y=D.Y and A.Z=D.Z where (B.X is not null and B.Y is not null and B.Z is not null) and (C.X is not null and C.Y is not null and C.Z is not null) and (D.X is not null and D.Y is not null and D.Z is not null) limit 0,30
安装了mysql5就可以用大家推荐的语句了 select x,y,z from a where not exists (select 1 from( select x,y,z from b union select x,y,z from c union select x,y,z from d )t where x = a.x and y = a.y and z = a.z )这个很好,想问问其中的t指代的是三个表的并集表么?出了这个语句t表还存在并使用么?
where not exists(select 1 from b where a.x=b.x and a.y=b.y and a.z=b.z)
and not exists(select 1 from C where a.x=C.x and a.y=C.y and a.z=C.z)
and not exists(select 1 from D where a.x=D.x and a.y=D.y and a.z=D.z)
--2005select x,y,z from a
except
select x,y,z from b
except
select x,y,z from c
except
select x,y,z from d
select x,y,z
from a
where not exists (select 1 from(
select x,y,z from b
union
select x,y,z from c
union
select x,y,z from d
)t
where x = a.x and y = a.y and z = a.z
)
where not exists(select 1 from tbB where x=a.x and y=a.y and z=a.z)
and not exists(select 1 from tbC where x=a.x and y=a.y and z=a.z)
and not exists(select 1 from tbD where x=a.x and y=a.y and z=a.z)
except
(select * from B union select * from C union select * from D)
select * from tablea a
where not exists (select 1 from tableb where x=a.x and y=a.y and z=a.z)
and not exists (select 1 from tablec where x=a.x and y=a.y and z=a.z)
and not exists (select 1 from tabled where x=a.x and y=a.y and z=a.z)
SQL 语句 : SELECT * FROM `A`
where X not in (SELECT X FROM `B`) LIMIT 0, 30MySQL 返回:
You have an error in your SQL syntax near 'SELECT X FROM `B`) LIMIT 0, 30' at line 2
select A.X,A.Y,A.Z
from A left join B on A.X=B.X and A.Y=B.Y and A.Z=B.Z
left join C on A.X=C.X and A.Y=C.Y and A.Z=C.Z
left join D on A.X=D.X and A.Y=D.Y and A.Z=D.Z
where (B.X is not null and B.Y is not null and B.Z is not null)
and (C.X is not null and C.Y is not null and C.Z is not null)
and (D.X is not null and D.Y is not null and D.Z is not null)
limit 0,30
select x,y,z
from a
where not exists (select 1 from(
select x,y,z from b
union
select x,y,z from c
union
select x,y,z from d
)t
where x = a.x and y = a.y and z = a.z
)这个很好,想问问其中的t指代的是三个表的并集表么?出了这个语句t表还存在并使用么?