SQL查询中DUTY_COST字段返回0.00值无法过滤排除问题求助

问题原因 #

你遇到的0值记录问题核心是两点：

1. 你使用LEFT JOIN关联MPOEXP IV表，左连接会保留左表FGRECL F2的所有符合WHERE条件的记录，哪怕IV表没有匹配到CEID='DUTY'的行、或者匹配到的CEVA不大于0，你的CASE WHEN语句就会返回0值。
2. 你无法直接在WHERE里加DUTY_COST != 0过滤，是因为SQL执行顺序中，WHERE阶段早于SELECT阶段，SELECT里定义的别名DUTY_COST在WHERE执行时还未生成，无法识别。

另外注意你当前写的IV.CEVA > '0'是字符串比较逻辑，如果CEVA是数值类型字段，建议去掉单引号，避免隐式转换导致的判断错误。

解决方案 #

分两种场景提供可直接使用的修改方案：

场景1：仅需过滤DUTY_COST为0的记录，不需要合并多费用列 #

将现有查询套入CTE后再过滤，修改后完整语句如下：

WITH CTE_FGLEDG AS
(
SELECT * FROM FGLEDG
),
CTE_TEMP AS (
SELECT DISTINCT
F2.DIVI,
F2.FACI,
F2.WHLO,
MW.WHNM,
F2.SUNO,
ID.SUNM,
F2.PUNO,
F9.SINO,
F2.PNLI,
F2.PNLS,
MM.BUAR,
MM.PRGP,
MM.HDPR,
F2.ITNO,
MM.ITDS,
--MITLAD - MD.FUDS,
MH.TX15,
MH.TY15,
IA.DWDT,
IB.CODT,
IA.DUDT,
IB.ORQA,
IB.PUSL,
IB.PUST,
IB.OEND,
F9.ACQT,
IC.SUDO,
IB.PROJ,
IB.TEDL,
IB.MODL,
IA.CUCD,
IB.PUPR,
MT.TRPR,
CASE WHEN IV.CEID = 'DUTY' AND IV.CEVA > 0 THEN IV.CEVA ELSE 0 END AS DUTY_COST,
--CASE WHEN IV.CEID = 'DUTY/KG' THEN IV.CEVA ELSE 0 END AS DUTY_KG_COST,
--CASE WHEN IV.CEID = 'FRTIN' THEN IV.CEVA ELSE 0 END AS FREIGHT_COST,
--CASE WHEN IV.CEID = 'BRKG' THEN IV.CEVA ELSE 0 END AS BROKERAGE_COST,
--CASE WHEN IV.CEID = 'DUTY' THEN IV.WSOP ELSE 0 END AS DUTY_RATE,
F9.ARAT,
M9.CSNO
FROM FGRECL F2
LEFT JOIN CIDMAS ID ON ID.SUNO = F2.SUNO
LEFT JOIN FPLEDG EP ON EP.SUNO = F2.SUNO
LEFT JOIN CTE_FGLEDG CTE ON EP.JRNO = CTE.JRNO
LEFT JOIN MITMAS MM ON F2.ITNO = MM.ITNO
--LEFT JOIN MITLAD MD ON MD.ITNO = MM.ITNO AND MM.LNCD = 'GB'
LEFT JOIN MITMAH MH ON F2.ITNO = MH.ITNO
LEFT JOIN MPHEAD IA ON F2.PUNO = IA.PUNO
LEFT JOIN MPLINE IB ON F2.PUNO = IB.PUNO AND F2.ITNO = IB.ITNO
LEFT JOIN MPLIND IC ON F2.PUNO = IC.PUNO AND IC.PUOS = '45'
LEFT JOIN MITFAC M9 ON F2.ITNO = M9.ITNO AND F2.FACI = M9.FACI
LEFT JOIN MITWHL MW ON F2.WHLO = MW.WHLO
LEFT JOIN MPOEXP IV ON F2.PUNO = IV.PUNO AND F2.PNLI = IV.PNLI AND F2.PNLS = IV.PNLS AND F2.CUCD = IV.CUCD
LEFT JOIN MITTRA MT ON MT.RIDN = F2.PUNO AND MT.RIDL = F2.PNLI AND MT.WHLO = F2.WHLO AND MT.ITNO = F2.ITNO
LEFT JOIN FGINAE F9 ON F9.CONO = F2.CONO AND F9.DIVI = F2.DIVI AND F9.SUNO = F2.SUNO AND F9.PUNO = F2.PUNO AND F9.PNLI = F2.PNLI
    AND EP.CONO = CTE.CONO
    AND EP.DIVI = CTE.DIVI
WHERE F2.CONO = '100'
    AND F2.SUNO = '6319'
    AND F2.PUNO = '5330001073'
    AND F2.ITNO = 'P000003-0001S'
    AND CTE.AIT6 <> ''
)
SELECT * FROM CTE_TEMP 
WHERE DUTY_COST > 0
ORDER BY PUNO, PNLI, PNLS

场景2：需要拆分多费用列到同一行（符合你给出的多列预期结果） #

你出现重复行的本质是同一行主数据匹配到了MPOEXP表中多条不同CEID的费用记录，用分组聚合可以直接合并为一行，同时过滤DUTY_COST为0的记录，修改后语句如下：

WITH CTE_FGLEDG AS
(
SELECT * FROM FGLEDG
)
SELECT 
F2.DIVI,
F2.FACI,
F2.WHLO,
MAX(MW.WHNM) AS WHNM,
F2.SUNO,
MAX(ID.SUNM) AS SUNM,
F2.PUNO,
MAX(F9.SINO) AS SINO,
F2.PNLI,
F2.PNLS,
MAX(MM.BUAR) AS BUAR,
MAX(MM.PRGP) AS PRGP,
MAX(MM.HDPR) AS HDPR,
F2.ITNO,
MAX(MM.ITDS) AS ITDS,
MAX(MH.TX15) AS TX15,
MAX(MH.TY15) AS TY15,
MAX(IA.DWDT) AS DWDT,
MAX(IB.CODT) AS CODT,
MAX(IA.DUDT) AS DUDT,
MAX(IB.ORQA) AS ORQA,
MAX(IB.PUSL) AS PUSL,
MAX(IB.PUST) AS PUST,
MAX(IB.OEND) AS OEND,
MAX(F9.ACQT) AS ACQT,
MAX(IC.SUDO) AS SUDO,
MAX(IB.PROJ) AS PROJ,
MAX(IB.TEDL) AS TEDL,
MAX(IB.MODL) AS MODL,
MAX(IA.CUCD) AS CUCD,
MAX(IB.PUPR) AS PUPR,
MAX(MT.TRPR) AS TRPR,
SUM(CASE WHEN IV.CEID = 'DUTY' AND IV.CEVA > 0 THEN IV.CEVA ELSE 0 END) AS DUTY_COST,
SUM(CASE WHEN IV.CEID = 'DUTY/KG' THEN IV.CEVA ELSE 0 END) AS DUTY_KG_COST,
SUM(CASE WHEN IV.CEID = 'FRTIN' THEN IV.CEVA ELSE 0 END) AS FREIGHT_COST,
SUM(CASE WHEN IV.CEID = 'BRKG' THEN IV.CEVA ELSE 0 END) AS BROKERAGE_COST,
MAX(F9.ARAT) AS ARAT,
MAX(M9.CSNO) AS CSNO
FROM FGRECL F2
LEFT JOIN CIDMAS ID ON ID.SUNO = F2.SUNO
LEFT JOIN FPLEDG EP ON EP.SUNO = F2.SUNO
LEFT JOIN CTE_FGLEDG CTE ON EP.JRNO = CTE.JRNO
LEFT JOIN MITMAS MM ON F2.ITNO = MM.ITNO
LEFT JOIN MITMAH MH ON F2.ITNO = MH.ITNO
LEFT JOIN MPHEAD IA ON F2.PUNO = IA.PUNO
LEFT JOIN MPLINE IB ON F2.PUNO = IB.PUNO AND F2.ITNO = IB.ITNO
LEFT JOIN MPLIND IC ON F2.PUNO = IC.PUNO AND IC.PUOS = '45'
LEFT JOIN MITFAC M9 ON F2.ITNO = M9.ITNO AND F2.FACI = M9.FACI
LEFT JOIN MITWHL MW ON F2.WHLO = MW.WHLO
LEFT JOIN MPOEXP IV ON F2.PUNO = IV.PUNO AND F2.PNLI = IV.PNLI AND F2.PNLS = IV.PNLS AND F2.CUCD = IV.CUCD
LEFT JOIN MITTRA MT ON MT.RIDN = F2.PUNO AND MT.RIDL = F2.PNLI AND MT.WHLO = F2.WHLO AND MT.ITNO = F2.ITNO
LEFT JOIN FGINAE F9 ON F9.CONO = F2.CONO AND F9.DIVI = F2.DIVI AND F9.SUNO = F2.SUNO AND F9.PUNO = F2.PUNO AND F9.PNLI = F2.PNLI
    AND EP.CONO = CTE.CONO
    AND EP.DIVI = CTE.DIVI
WHERE F2.CONO = '100'
    AND F2.SUNO = '6319'
    AND F2.PUNO = '5330001073'
    AND F2.ITNO = 'P000003-0001S'
    AND CTE.AIT6 <> ''
GROUP BY F2.DIVI, F2.FACI, F2.WHLO, F2.SUNO, F2.PUNO, F2.PNLI, F2.PNLS, F2.ITNO
HAVING SUM(CASE WHEN IV.CEID = 'DUTY' AND IV.CEVA > 0 THEN IV.CEVA ELSE 0 END) > 0
ORDER BY F2.PUNO, F2.PNLI, F2.PNLS

内容的提问来源于stack exchange，提问作者KCS