问一个sql语句如何写有A B C D E F五张表(6个表都有主见). 其中, A 与 B是多对多的关系(通过F表关联), A 与C是一对多的关系, B与c是多对多的关系(通过D关联).B和E是多对一的关系. 现在打算写一个sql语句,查询A和B,E的数据,要求A/B的数据在F都存在,并且,如果B的数据在D存在,那么与此关联的C的数据必须与A表有关联;E的数据与B的数据有关联,才查询出来。 select A.key,B.key,E.key from A join F on F.key=A.key
join B on B.key=F.key
join E on E.key=B.key --this line has question
left join C on C.key=A.key
left join D on D.key=C.key
and B.key=D.key this line has question
join B on B.key=F.key
join E on E.key=B.key --this line has question
left join C on C.key=A.key
left join D on D.key=C.key
and B.key=D.key this line has question
SELECT DISTINCT
A.KEY,
B.KEY,
E.KEY
FROM A,
B,
C,
E,
F
WHERE A.KEY = F.KEY
AND B.KEY = F.KEY
AND B.KEY = D.KEY
AND C.KEY = D.KEY
AND A.KEY = C.KEY
AND B.KEY = E.KEY;
B.KEY,
E.KEY
FROM A,
B,
C,
E,
F
WHERE A.KEY = F.KEY
AND B.KEY = F.KEY
AND B.KEY = D.KEY
AND C.KEY = D.KEY
AND A.KEY = C.KEY
AND B.KEY = E.KEY;
(select distinct Ak,Bk from
(
(select A.key as Ak,B.key as Bk
from A,B,F
where A.key=F.key
and B.key=F.key)
union
(select A.key as Ak,B.key as Bk
from A,B,C,D
where B.key=D.key
and D.key=C.key
and C.key=A.key)
)) T1 left join E on T1.Bk=E.key
(select A.key as Ak,B.key as Bk
from A,B,C,D
where B.key=D.key
and D.key=C.key
and C.key=A.key)
你这样写,那么A和B之间的关系就没有了,我的题意是两种情况下,AB都通过F发生关联,而C和D,E的数据满足条件才被查询出来
-- 1ST WAY:
SELECT A.KEY,
B.KEY,
E.KEY
FROM A,
B,
E,
F
WHERE A.KEY = F.KEY
AND B.KEY = F.KEY
AND E.KEY = B.KEY(+)
AND (EXISTS (
SELECT 1
FROM C,
D
WHERE B.KEY = D.KEY
AND D.KEY = C.KEY
AND A.KEY = C.KEY
)
OR 1 = 1);2ND WAY:
用UNION + INTERSECT 实现,因为效率不高就没贴出了。TRY IT ..
from
(select A.key as Ak,B.key as Bk
from A,B,F
where A.key=F.key
and B.key=F.key) T1 left join
(select B.key as Bk,E.key as Ek
from A,B,C,D,E,F
where A.key=C.key
and C.key=D.key
and D.key=B.key
and B.key=E.key
and A.key=F.key
and B.key=F.key) T2 on T1.Bk=T2.Bk-----------------------------------------------------
如果E中数据均能由B中数据唯一确定(你说E和B是一对多,所以我觉得可以),
则去掉T2中A,B,F的关联条件仍可保证结果集与F关联, 则上式可简写为:select T1.Ak,T1.Bk,T2.Ek
from
(select A.key as Ak,B.key as Bk
from A,B,F
where A.key=F.key
and B.key=F.key) T1 left join
(select B.key as Bk,E.key as Ek
from A,B,C,D,E
where A.key=C.key
and C.key=D.key
and D.key=B.key
and B.key=E.key) T2 on T1.Bk=T2.Bk
你的exists字句不完整,应该去掉1=1,改成以下代码:
EXISTS (SELECT 1
FROM D,C
WHERE D.B_key = B.B_key
AND D.C_key=C.C_key
AND C.A_key = A.A_key
)
OR NOT EXISTS (SELECT 1 ----- 如果D中数据不存在,则不需要检查其他条件
FROM D
WHERE D.B_key = B.B_key
)
作用和
OR NOT EXISTS (SELECT 1 ----- 如果D中数据不存在,则不需要检查其他条件
FROM D
WHERE D.B_key = B.B_key
)
差不多。