SQL SERVER2000中的多表联合查询程序如下:
SELECT * FROM
(SELECT pp.HTNO, pcd.NAME AS LD1 FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.LD_1_iD=pcd.DEViCE_iD WHERE pp.PLAN_STYLE<>-1 ) AS A
LEFT JOiN
(SELECT pp.HTNO, pcd.NAME AS LD2 FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.LD_2_iD=pcd.DEViCE_iD) AS B ON A.HTNO = B.HTNO
LEFT JOiN
(SELECT pp.HTNO,pp.PONO ,pp.SG,pp.D_DECiSiON_SG FROM PUBLiC_PLAN pp) AS C ON A.HTNO = C.HTNO
LEFT JOiN (SELECT pp.HTNO, pcd.NAME AS CAST1,pp.D_TiME_SUBTRACT FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.CAST_1_iD = pcd.DEViCE_iD) AS D ON A.HTNO = D.HTNO
ORDER BY SUBSTRING(CAST(A.HTNO AS CHAR(6)),2,5) 在oracle10g中怎么实现上述程序的多表查询
SELECT * FROM
(SELECT pp.HTNO, pcd.NAME AS LD1 FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.LD_1_iD=pcd.DEViCE_iD WHERE pp.PLAN_STYLE<>-1 ) AS A
LEFT JOiN
(SELECT pp.HTNO, pcd.NAME AS LD2 FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.LD_2_iD=pcd.DEViCE_iD) AS B ON A.HTNO = B.HTNO
LEFT JOiN
(SELECT pp.HTNO,pp.PONO ,pp.SG,pp.D_DECiSiON_SG FROM PUBLiC_PLAN pp) AS C ON A.HTNO = C.HTNO
LEFT JOiN (SELECT pp.HTNO, pcd.NAME AS CAST1,pp.D_TiME_SUBTRACT FROM PUBLiC_PLAN pp JOiN PUBLiC_CODE_DEViCE pcd ON pp.CAST_1_iD = pcd.DEViCE_iD) AS D ON A.HTNO = D.HTNO
ORDER BY SUBSTRING(CAST(A.HTNO AS CHAR(6)),2,5) 在oracle10g中怎么实现上述程序的多表查询
解决方案 »
- 求大神解答,两个ORACLE查询的问题
- 求助,merge into可以并发吗?
- jdbc连接数据库,取返回值
- 在集群环境下如何安装ORACLE?
- Oracle如何配置透明网关访问SQLServer?
- 一个数据文件恢复的问题
- 使用sql语句新建oracle数据库怎样指定数据库的用户和及其对应的用户密码呢?
- 求一个把表记录导入到文本文件,并且带‘|’分隔符。(MySQL)
- 数据导出问题,急!!求救!!
- 谁用过sco unix上面的oracle,能否给些建议?
- oracle中是否可以像SQL Server2000一样开发扩展存储过程调用外部DLL?
- 怎么能够测试SQL在ORALCE上的第一次执行时间?
问题子查询太多,可能性能不好
把
ORDER BY SUBSTRING(CAST(A.HTNO AS CHAR(6)),2,5)
改成
ORDER BY SUBSTR(CAST(A.HTNO AS CHAR(6)),2,5)
就可以了。
在10g上可以用这样的。把
ORDER BY SUBSTRING(CAST(A.HTNO AS CHAR(6)),2,5)
改成
ORDER BY SUBSTR(CAST(A.HTNO AS CHAR(6)),2,5)
就可以了。