多表联查
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
[其他解释]
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 (
SELECT '1' AS id,'C1' AS pid,'D1' AS sid FROM DUAL
UNION ALL
SELECT '1' AS id,'C1' AS pid,'D2' AS sid FROM DUAL
UNION ALL
SELECT '1' AS id,'C2' AS pid,'D3' AS sid FROM DUAL
UNION ALL
SELECT '1' AS id,'C3' AS pid,'D3' AS sid FROM DUAL
UNION ALL
SELECT '2' AS id,'E1' AS pid,'F1' AS sid FROM DUAL
UNION ALL
SELECT '2' AS id,'E1' AS pid,'F2' AS sid FROM DUAL
UNION ALL
SELECT '2' AS id,'E2' AS pid,'F3' AS sid FROM DUAL
UNION ALL
SELECT '2' AS id,'E3' AS pid,'F3' AS sid FROM DUAL
)
,
C AS (
SELECT 'C1' AS pid,'C1-NAME' AS pname FROM DUAL
UNION ALL
SELECT 'C2' AS pid,'C2-NAME' AS pname FROM DUAL
UNION ALL
SELECT 'C3' AS pid,'C3-NAME' AS pname FROM DUAL
UNION ALL
SELECT 'E1' AS pid,'E1-NAME' AS pname FROM DUAL
UNION ALL
SELECT 'E2' AS pid,'E2-NAME' AS pname FROM DUAL
UNION ALL
SELECT 'E3' AS pid,'E3-NAME' AS pname FROM DUAL
),
D AS (
SELECT 'D1' AS sid,'D1-NAME' AS sname FROM DUAL
UNION ALL
SELECT 'D2' AS sid,'D2-NAME' AS sname FROM DUAL
UNION ALL
SELECT 'D3' AS sid,'D3-NAME' AS sname FROM DUAL
UNION ALL
SELECT 'F1' AS sid,'F1-NAME' AS sname FROM DUAL
UNION ALL
SELECT 'F2' AS sid,'F2-NAME' AS sname FROM DUAL
UNION ALL
SELECT 'F3' AS sid,'F3-NAME' AS sname FROM DUAL
),
E AS (
SELECT 'A1' AS agentid,'A1-NAME' AS agentname FROM DUAL
UNION ALL
SELECT 'A2' AS agentid,'A2-NAME' AS agentname FROM DUAL
UNION ALL
SELECT 'B1' AS agentid,'B1-NAME' AS agentname FROM DUAL
UNION ALL
SELECT 'B2' AS agentid,'B2-NAME' AS agentname FROM DUAL
)
SELECT DISTINCT A.* 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 E1 ON a.createagentid = E1.agentid
LEFT JOIN E E2 ON a.verifagentid = E2.agentid