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等连接.写不出来了...
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等连接.写不出来了...
解决方案 »
- DataGuard,主库在Linux5 64bit上面 ,从库可以安装在 Windows2008 64bit 上吗?
- 菜鸟关于10g数据库级闪回的疑问~
- java.sql.SQLException: 无法从套接字读取更多的数据
- 导入另一台电脑的.txt文件数据到本地oracle数据库
- 求助呀!!!关于触发器,那位高手可以故障解决下
- split partition 用法
- java如何获取SQL分析结果
- oracle数据库
- 怎么样按照时间调用触发器以及在触发器中调用存储过程
- 类似nvl的写法
- 连接ORACLE数据库出了问题,很奇怪的问题,请指教下。。。。
- oracle:在触发器中,如何获得自增id的值,并实现更新数据!
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
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)
试试。
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再不是就
(SELECT DISTINCT id FROM(
SELECT * FROM B b INNER JOIN C c ON b.pid=c.pid INNER JOIN D d ON b.sid=d.sid
) f) INNER JOIN A a ON id=a.id
INNER JOIN E e ON a.createagentid=e.agentid
INNER JOIN E f ON a.verifyagentid=f.agentid;
这样就可以了!!!!因为是存储过程,所以查询条件可以放在SQL语句之外