记一次“exists”与“in”的使用与理解
WHERE
...
AND TBL1.cus_nnd_sirk || TBL1.status IN (
SELECT
TBL1.cus_nnd_sirk || MAX(TBL1.status)
FROM
BODATA.TSY120_010 TBL1
GROUP BY
TBL1.cus_nnd_sirk
)
WHERE
...
AND TBL1.status = (
SELECT
MAX(TBL.status) AS status
FROM
BODATA.TSY120_010 TBL
WHERE
TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk
AND TBL1.status = TBL.status
GROUP BY
TBL.cus_nnd_sirk
)
WHERE
...
AND TBL1.status = (
SELECT
TBL1.status
FROM
(
SELECT
cus_nnd_sirk
, MAX(status) AS status
FROM
BODATA.TSY120_010
GROUP BY
cus_nnd_sirk
) TBL
WHERE
TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk
AND TBL1.status = TBL.status
)
WHERE
...
AND EXISTS (
SELECT
TBL1.status
FROM
(
SELECT
cus_nnd_sirk
, MAX(status) AS status
FROM
BODATA.TSY120_010
GROUP BY
cus_nnd_sirk
) TBL
WHERE
TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk
AND TBL1.status = TBL.status
)
结论是,有明显的提升。