SELECT A.*,'' AS HMD,'' AS ZDJCYY,C.CONS_UNIT_ID, C.CONS_UNIT_NAME,C.PLAN_BEGIN_DATE,
C.PLAN_END_DATE,C.Cons_Unit_Addr,C.PROJECT_MANAGER,C.PROJECT_MANAGER_PHONE
,(SELECT DISTINCT MIN(E.CREAT_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SQRQ_QY
,(SELECT DISTINCT MAX(E.LAST_UPDATE_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SLRQ_QY
FROM SBGL_T_SQZT A
LEFT OUTER JOIN SBGL_T_DW_SGGZ_SQB C ON A.ID = C.SQ_ID
WHERE (not A.ID is null) AND CLZT<>'*010'像这样的语句在sql中执行效率很高,但在oracle中执行很慢,原因是中间的这2句
,(SELECT DISTINCT MIN(E.CREAT_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SQRQ_QY
,(SELECT DISTINCT MAX(E.LAST_UPDATE_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SLRQ_QY不知道如果解决,请教高手。
C.PLAN_END_DATE,C.Cons_Unit_Addr,C.PROJECT_MANAGER,C.PROJECT_MANAGER_PHONE
,(SELECT DISTINCT MIN(E.CREAT_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SQRQ_QY
,(SELECT DISTINCT MAX(E.LAST_UPDATE_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SLRQ_QY
FROM SBGL_T_SQZT A
LEFT OUTER JOIN SBGL_T_DW_SGGZ_SQB C ON A.ID = C.SQ_ID
WHERE (not A.ID is null) AND CLZT<>'*010'像这样的语句在sql中执行效率很高,但在oracle中执行很慢,原因是中间的这2句
,(SELECT DISTINCT MIN(E.CREAT_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SQRQ_QY
,(SELECT DISTINCT MAX(E.LAST_UPDATE_DATE) FROM sbgl_T_ZTGZ E WHERE E.ID=A.ID) AS SLRQ_QY不知道如果解决,请教高手。
解决方案 »
- 默认安装的Oracle数据库要如何做优化?
- 当插入遇到主键重复,如何让数据库返回重复记录的那个rowid?
- select 前25%的记录
- 求看看我写的一个select,感觉很不对啊
- 200MB的数据入blob多长时间是正常的?
- 求助:根据不同的记录写不同的条件
- oracle客戶端配置問題
- 很简单的SQL语句语法问题,在线等,解决问题即结帖,解答者独得50分(本人无未结帖记录)
- 我是个小鸟 那位可以告诉我 oracle8i如何导入导出数据 如何才能登陆到oracle management server
- 如何在plus/sql中执行*.sql文件?
- 关于insert的语句从查询获得数据,并且从序列获得ID,该如何写?
- pc文件编译后链接错误(mingw)
SELECT A.*,
'' AS HMD,
'' AS ZDJCYY,
C.CONS_UNIT_ID,
C.CONS_UNIT_NAME,
C.PLAN_BEGIN_DATE,
C.PLAN_END_DATE,
C.CONS_UNIT_ADDR,
C.PROJECT_MANAGER,
C.PROJECT_MANAGER_PHONE,
D.MI AS SQRQ_QY,
D.MA AS SLRQ_QY
FROM SBGL_T_SQZT A,
SBGL_T_DW_SGGZ_SQB C,
(SELECT E.ID, MAX(E.LAST_UPDATE_DATE) MA, MIN(E.CREAT_DATE) MI
FROM SBGL_T_ZTGZ E
GROUP BY E.ID) D
WHERE B.ID = A.ID
AND A.ID(+) = C.SQ_ID
AND A.ID IS NOT NULL
AND CLZT <> '*010';
'' AS HMD,
'' AS ZDJCYY,
C.CONS_UNIT_ID,
C.CONS_UNIT_NAME,
C.PLAN_BEGIN_DATE,
C.PLAN_END_DATE,
C.CONS_UNIT_ADDR,
C.PROJECT_MANAGER,
C.PROJECT_MANAGER_PHONE,
D.MI AS SQRQ_QY,
D.MA AS SLRQ_QY
FROM SBGL_T_SQZT A,
SBGL_T_DW_SGGZ_SQB C,
(SELECT E.ID, MAX(E.LAST_UPDATE_DATE) MA, MIN(E.CREAT_DATE) MI
FROM SBGL_T_ZTGZ E
GROUP BY E.ID) D
WHERE D.ID = A.ID
AND A.ID(+) = C.SQ_ID
AND A.ID IS NOT NULL
AND CLZT <> '*010';