Oracle条件计数修改需求：排除DESTZIP为'00000'的行

方案一（通过差值计算无有效邮编的行数） #

SELECT 
    COUNT(*) AS TOTAL_ITEMS, 
    COUNT(CASE WHEN DESTZIP IS NOT NULL AND DESTZIP != '00000' THEN 1 END) AS TOTAL_WITH_ZIP, 
    COUNT(*) - COUNT(CASE WHEN DESTZIP IS NOT NULL AND DESTZIP != '00000' THEN 1 END) AS TOTAL_NO_DESTZIP
FROM MYTABLE
WHERE LASTMODIFIEDON BETWEEN TO_TIMESTAMP('06/04/2024','mm/dd/yyyy') AND TO_TIMESTAMP('06/05/2024','mm/dd/yyyy');

方案二（直接统计无有效邮编的行数） #

SELECT 
    COUNT(*) AS TOTAL_ITEMS, 
    COUNT(CASE WHEN DESTZIP IS NOT NULL AND DESTZIP != '00000' THEN 1 END) AS TOTAL_WITH_ZIP, 
    COUNT(CASE WHEN DESTZIP IS NULL OR DESTZIP = '00000' THEN 1 END) AS TOTAL_NO_DESTZIP
FROM MYTABLE
WHERE LASTMODIFIEDON BETWEEN TO_TIMESTAMP('06/04/2024','mm/dd/yyyy') AND TO_TIMESTAMP('06/05/2024','mm/dd/yyyy');

关键说明 #

• TOTAL_WITH_ZIP：用CASE表达式筛选出DESTZIP非空且不等于'00000'的记录，COUNT函数会忽略CASE返回的NULL值，仅统计符合条件的行数。
• TOTAL_NO_DESTZIP：两种方案结果完全一致，方案一通过总条数减去有效邮编行数得到，方案二则直接统计DESTZIP为空或等于'00000'的记录数，可根据习惯选择。

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