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等连接.写不出来了...
解决方案 »
- 菜鸟问题
- 重复记录筛选 高手赐教 在线等
- 请帮忙看看这个create table有什么问题?
- 不同平台下,oracle导出导入的 表空间的问题!
- 数据库无法启动服务10g
- 用console连接数据库时提示:没有监听器。添加本地网络服务名,输入本机IP,连接不成功,改成127.0.0.1才可以???
- 在ORACLE中建快照时storage(initial 512 next 512 pctincrease 10) refresh fast sysdate + 1时总是报错
- 如何在一个机器上安装和启动两个数据库实例?
- 关于表空间的疑问
- 求助大神~ oracle 按半年统计问题
- 连接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语句之外