筛选重复数据速度比较

SELECT
A.CUSTOMER_CONNECTION_CODE,
A.CUSTOMER_NAME,
A.DEALER_CONNECTION_CODE,
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG,
A.INSERT_DATE
FROM
KAMS_M_CUSTOMER A
WHERE (DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL) IN
(SELECT DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL
FROM
KAMS_M_CUSTOMER CUS
WHERE TEL IS NOT NULL
GROUP BY DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL
HAVING COUNT(1) > 1)
UNION
SELECT
A.CUSTOMER_CONNECTION_CODE,
A.CUSTOMER_NAME,
A.DEALER_CONNECTION_CODE,
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG,
A.INSERT_DATE
FROM
KAMS_M_CUSTOMER A
WHERE (DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1) IN
(SELECT DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1
FROM
KAMS_M_CUSTOMER CUS
WHERE PORTABLE_TEL1 IS NOT NULL
GROUP BY DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1
HAVING COUNT(1) > 1)
ORDER BY CUSTOMER_NAME,DEALER_CONNECTION_CODE,EXIST_FLG DESC,INSERT_DATE ASC

查询出4674条数据 共用时0.6秒

SELECT
A.CUSTOMER_CONNECTION_CODE,
A.CUSTOMER_NAME,
A.DEALER_CONNECTION_CODE,
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG,
A.INSERT_DATE
FROM
KAMS_M_CUSTOMER A
WHERE EXISTS
(SELECT 1
FROM
KAMS_M_CUSTOMER CUS
WHERE CUS.PORTABLE_TEL1 IS NOT NULL AND A.PORTABLE_TEL1 = CUS.PORTABLE_TEL1
AND A.CUSTOMER_CONNECTION_CODE <> CUS.CUSTOMER_CONNECTION_CODE
AND A.DEALER_CONNECTION_CODE = CUS.DEALER_CONNECTION_CODE
AND A.CUSTOMER_NAME = CUS.CUSTOMER_NAME)
UNION
SELECT
A.CUSTOMER_CONNECTION_CODE,
A.CUSTOMER_NAME,
A.DEALER_CONNECTION_CODE,
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG,
A.INSERT_DATE
FROM
KAMS_M_CUSTOMER A
WHERE EXISTS
(SELECT 1
FROM
KAMS_M_CUSTOMER CUS
WHERE CUS.TEL IS NOT NULL AND A.TEL = CUS.TEL
AND A.CUSTOMER_CONNECTION_CODE <> CUS.CUSTOMER_CONNECTION_CODE
AND A.DEALER_CONNECTION_CODE = CUS.DEALER_CONNECTION_CODE
AND A.CUSTOMER_NAME = CUS.CUSTOMER_NAME)
ORDER BY CUSTOMER_NAME,DEALER_CONNECTION_CODE,EXIST_FLG DESC,INSERT_DATE ASC

查询出4674条数据 共用时0.5秒