sql - 如何排除 CHVRSN 的重复项并为 CHVRSN 设置最大值？

Question

如何排除 CHVRSN 的重复值和 CHVRSN 的最大值必须保留。CHVRSN 在表中：WRPDT.WSCLHP B

SELECT DISTINCT C.REGN ,C.SVDC,A.DELR,C.NAME, A.PANO, A.VIN,A.UDAT AS UPDATE_DATE ,A.WONO AS PAID_DATE,B.CHWONO AS DATE_2 ,B.CHAJDT AS ADJ_DATE,
CASE
WHEN PW = '4' THEN 'A'
WHEN PW = '4' THEN 'A'
WHEN PW = '0' THEN 'IP'
WHEN PW = '3' THEN 'IP'
WHEN PW = '2' THEN 'R'
ELSE 'OTHER'
END AS STATUS,
CHVRSN, CHSB

FROM WRPDT.WPPWP A

LEFT OUTER JOIN WRPDT.WSCLHP B
ON A.PWF = B.CHF
AND A.PWLR=B.CDLR
AND A.WONO=B.CHNO

LEFT OUTER JOIN DLPDT.DRDMFP C
ON A.PWDLR = C.DMLR

WHERE SYS2 = '20'
and cdat > 20180809
AND EAMT > 1000
and pw not in ('90', '95', '05') 

AND EXISTS
(SELECT * FROM CADAT.CCAMP WHERE CMIN=PVIN AND (CMCAMP IN ('953')))

ORDER BY 3, 5

标签： sqldb2max

Answer 1

像这样我建议

SELECT DISTINCT C.REGN ,C.SVDC,A.DELR,C.NAME, A.PANO, A.VIN,A.UDAT AS UPDATE_DATE ,A.WONO AS PAID_DATE,B.CHWONO AS DATE_2 ,B.CHAJDT AS ADJ_DATE,
CASE
WHEN PW = '4' THEN 'A'
WHEN PW = '4' THEN 'A'
WHEN PW = '0' THEN 'IP'
WHEN PW = '3' THEN 'IP'
WHEN PW = '2' THEN 'R'
ELSE 'OTHER'
END AS STATUS,
CHVRSN, CHSB

FROM WRPDT.WPPWP A

LEFT OUTER JOIN (
    SELECT * 
    FROM WRPDT.WSCLHP
    WHERE CHVRSN IN (SELECT MAX(CHVRSN) FROM WRPDT.WSCLHP) 
    ) AS B
ON A.PWF = B.CHF
AND A.PWLR=B.CDLR
AND A.WONO=B.CHNO

LEFT OUTER JOIN DLPDT.DRDMFP C
ON A.PWDLR = C.DMLR

WHERE SYS2 = '20'
and cdat > 20180809
AND EAMT > 1000
and pw not in ('90', '95', '05') 

AND EXISTS
(SELECT * FROM CADAT.CCAMP WHERE CMIN=PVIN AND (CMCAMP IN ('953')))

ORDER BY 3, 5

顺便说一句，您的代码可能更清晰。并非所有列引用都是合格的，所以我只在子选择中包含了明确需要的那些。