关于oracle的一个问题

[ning823]

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

不知道如果解决，请教高手。

[oracledbalgtu]

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';

[Quote=引用楼主 ning823 的帖子:]
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 …
[/Quote]

[oracledbalgtu]

上面的把d.id错写成b.id了，改正后，如下：

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 D.ID = A.ID
   AND A.ID(+) = C.SQ_ID
   AND A.ID IS NOT NULL
   AND CLZT <> '*010';

[Quote=引用 1 楼 oracledbalgtu 的回复:]
SQL code
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)…
[/Quote]