如何合并仅WHERE条件不同的两个相似SQL查询实现分条件统计

现有2个结构完全一致的SQL查询，仅WHERE子句的筛选条件存在差异，需要合并查询结果，实现按plateNo维度分组，同一行同时展示两类筛选条件下的统计值workingcards_1、workingcards_2。

原第一个查询：

SELECT plateNo,
       SUM(cardno) AS workingcards_1
FROM 
      (select a.plateNo,
      COUNT(DISTINCT cardno) as cardno
from(select *, use_time
     from card_reader)cr
Inner join meter m
    ON cr.use_time BETWEEN m.start and m.end
    WHERE cr.case IN (first, second, third)
GROUP BY m.plateNo)
GROUP BY plateNo;

原第二个查询：

SELECT plateNo,
       SUM(cardno) AS workingcards_2
FROM 
      (select a.plateNo,
      COUNT(DISTINCT cardno) as cardno
from(select *, use_time
     from card_reader)cr
Inner join meter m
    ON cr.use_time BETWEEN m.start and m.end
    WHERE cr.case NOT IN (first, second, third)
GROUP BY m.plateNo)
GROUP BY plateNo;

预期返回结构：

直接用条件聚合完成统计，不需要拆分两次查询，也不需要做结果集关联，一次表关联+分组即可得到结果，同时去掉了原查询中冗余的嵌套子查询和无意义的外层聚合：

SELECT 
    m.plateNo,
    COUNT(DISTINCT CASE WHEN cr.case IN (first, second, third) THEN cr.cardno END) AS workingcards_1,
    COUNT(DISTINCT CASE WHEN cr.case NOT IN (first, second, third) THEN cr.cardno END) AS workingcards_2
FROM card_reader cr
INNER JOIN meter m
    ON cr.use_time BETWEEN m.start AND m.end
GROUP BY m.plateNo;

• 原查询存在冗余：内层子查询已经按plateNo分组完成了cardno的去重计数，外层再套一层SUM+GROUP BY plateNo完全是重复操作，没有实际作用
• 条件聚合逻辑：COUNT(DISTINCT ...)会自动忽略CASE WHEN不满足条件时返回的NULL值，分别统计两类场景下的去重卡号数，和原两个查询的统计逻辑完全一致
• 若存在某plateNo仅符合其中一类筛选条件的情况，对应另一类统计值会返回0，不会出现缺行问题

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