FULL JOIN 搞死我了 ……高手进来

Question

SELECT *
FROM (SELECT AFTERMARKET, SUM(LFIMG) AS LFIMG, NAME1
FROM (SELECT R.AFTERMARKET, ABS(LFIMG) AS LFIMG, NAME1
FROM ROS_SALES_DATA@ROS_LINK_TO_AIGUSER T,
RAP_COMPANY_CRMTOROS@ROS_LINK_TO_AIGUSER R
WHERE T.KUNNR = R.SELL
AND T.AUART IN
('ZEOR', 'ZIOR', 'ZKO', 'ZOOR', 'ZKE', 'ZSDS')) F
GROUP BY AFTERMARKET, NAME1) A
FULL JOIN (SELECT AFTERMARKET, SUM(ABS(TLFIMG)) AS TLFIMG, NAME1
FROM (SELECT R.AFTERMARKET,
TO_NUMBER(T.LFIMG) AS TLFIMG,
NAME1
FROM ROS_SALES_DATA@ROS_LINK_TO_AIGUSER T,
RAP_COMPANY_CRMTOROS@ROS_LINK_TO_AIGUSER R
WHERE R.AFTERMARKET = T.KUNNR
AND T.AUART IN ('ZDNE', 'ZDRE')) C
GROUP BY C.AFTERMARKET, C.NAME1) B

ON A.AFTERMARKET = B.AFTERMARKET;

SQL在上
在pl/sql中能查出来，当点击显示下一页显示行数超过100行就会执行很久都没结果
在程序中就会直接卡死不出来
求高手指教………………最好代替full join

Answer 1

试试以下改进的SQL语句：

WITH 
  T1 AS (
    SELECT R.AFTERMARKET, ABS(LFIMG) AS LFIMG, NAME1
    FROM ROS_SALES_DATA@ROS_LINK_TO_AIGUSER T
    JOIN RAP_COMPANY_CRMTOROS@ROS_LINK_TO_AIGUSER R
    ON T.KUNNR = R.SELL
    WHERE T.AUART IN ('ZEOR', 'ZIOR', 'ZKO', 'ZOOR', 'ZKE', 'ZSDS')
  ),
  T2 AS (
    SELECT R.AFTERMARKET, TO_NUMBER(T.LFIMG) AS TLFIMG, NAME1
    FROM ROS_SALES_DATA@ROS_LINK_TO_AIGUSER T
    JOIN RAP_COMPANY_CRMTOROS@ROS_LINK_TO_AIGUSER R
    ON R.AFTERMARKET = T.KUNNR
    WHERE T.AUART IN ('ZDNE', 'ZDRE')
  ),
  T3 AS (
    SELECT AFTERMARKET, SUM(LFIMG) AS LFIMG, NAME1
    FROM T1
    GROUP BY AFTERMARKET, NAME1
  ),
  T4 AS (
    SELECT AFTERMARKET, SUM(ABS(TLFIMG)) AS TLFIMG, NAME1
    FROM T2
    GROUP BY AFTERMARKET, NAME1
  )
SELECT T3.AFTERMARKET, T3.LFIMG, T4.TLFIMG, T3.NAME1
FROM T3
JOIN T4
ON T3.AFTERMARKET = T4.AFTERMARKET;

这样可以减少行数并加速查询。