多表联查 A表:id,createagentid,createtime,verifagentid,verifytime,status
B表:id,pid,sid,
C表:pid,pname,
D表:sid,sname,
E表:agentid,agentname
A.id=B.id
a.creteagentid=E.agentid
a.verifyagentid=E.agentid
B.pid=C.pid
B.sid=D.sid
其中A为主表,A.id在B中会有多个.B.pid,sid在C,D中只有一个.
查询条件为C.pname,D.sname,
显示数据为:A.*,不重复
我试过inner join,left join ,right join等连接.写不出来了... ------最佳解决方案-------------------- SELECT * FROM A
INNER JOIN B ON A.id = B.id
LEFT JOIN C ON B.pid = C.pid
LEFT JOIN D ON B.sid = D.sid
LEFT JOIN E AS E1 ON a.creteagentid = E1.agentid
LEFT JOIN E AS E2 ON a.verifyagentid = E2.agentid ------其他解决方案-------------------- 亲,这样查出来不是我要的结果.我要的只要A.*不重复的 ------其他解决方案-------------------- SELECT * FROM A
INNER JOIN (SELECT DISTINCT id FROM B) B ON A.id = B.id
LEFT JOIN C ON B.pid = C.pid
LEFT JOIN D ON B.sid = D.sid
LEFT JOIN E AS E1 ON a.creteagentid = E1.agentid
LEFT JOIN E AS E2 ON a.verifyagentid = E2.agentid
或
SELECT * FROM A
LEFT JOIN C ON B.pid = C.pid
LEFT JOIN D ON B.sid = D.sid
LEFT JOIN E AS E1 ON a.creteagentid = E1.agentid
LEFT JOIN E AS E2 ON a.verifyagentid = E2.agentid
WHERE EXISTS(SELECT 'X' FROM B WHERE A.ID = B.ID)
试试。 ------其他解决方案-------------------- 两结果一样的吧,我运行了,没有区别呀 ------其他解决方案--------------------
WITH A AS (
SELECT '1' AS id,'A1' AS createagentid,'01:01:01' AS createtime,'B1' AS verifagentid,'01:01:01' AS verifytime,'1' AS status FROM DUAL
UNION ALL
SELECT '2' AS id,'A2' AS createagentid,'01:01:01' AS createtime,'B2' AS verifagentid,'01:01:01' AS verifytime,'1' AS status FROM DUAL
),
B AS (