如何在PostgreSQL中用单查询返回多WHERE条件的统计结果？

问题描述 #

我有两个统计员工总数的查询，仅最后两个AND条件存在差异，希望合并为一个查询，让结果按行返回：

第一个查询（统计姓名、姓氏均不为空的员工数） #

SELECT 
    COUNT(*)
FROM
(
SELECT
    E.NAME,
    E.LAST_NAME,
    E.BIRTH_DATE,
    E.ID
FROM
    EMPLOYEES E
WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
AND E.NAME IS NOT NULL 
AND E.LAST_NAME IS NOT NULL
GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE,E.ID
) AUX;

第二个查询（统计姓名、姓氏均为空的员工数） #

SELECT 
    COUNT(*)
FROM
(
SELECT
    E.NAME,
    E.LAST_NAME,
    E.BIRTH_DATE,
    E.ID
FROM
    EMPLOYEES E
WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
AND E.NAME IS NULL 
AND E.LAST_NAME IS NULL
GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE,E.ID
) AUX;

预期输出 #

解决方案 #

方法1：直接用UNION ALL合并（逻辑直观） #

完全保留原查询逻辑，通过UNION ALL将两个结果合并为两行：

SELECT COUNT(*) AS total
FROM (
    SELECT E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID
    FROM EMPLOYEES E
    WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
      AND E.NAME IS NOT NULL 
      AND E.LAST_NAME IS NOT NULL
    GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID
) AUX
UNION ALL
SELECT COUNT(*) AS total
FROM (
    SELECT E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID
    FROM EMPLOYEES E
    WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
      AND E.NAME IS NULL 
      AND E.LAST_NAME IS NULL
    GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID
) AUX;

方法2：优化分组统计（减少表扫描次数） #

通过分组条件一次性统计两种情况，仅扫描一次表，效率更高：

SELECT COUNT(*) AS total
FROM (
    SELECT E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID
    FROM EMPLOYEES E
    WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
      -- 过滤出符合两种条件的记录
      AND ((E.NAME IS NOT NULL AND E.LAST_NAME IS NOT NULL) OR (E.NAME IS NULL AND E.LAST_NAME IS NULL))
    GROUP BY E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID,
             -- 按条件分组，区分两种统计情况
             CASE WHEN E.NAME IS NOT NULL AND E.LAST_NAME IS NOT NULL THEN 1 ELSE 2 END
) AUX
GROUP BY CASE WHEN E.NAME IS NOT NULL AND E.LAST_NAME IS NOT NULL THEN 1 ELSE 2 END;

如果不需要保留原查询的子查询结构，还可以简化为：

SELECT COUNT(DISTINCT CONCAT(E.NAME, E.LAST_NAME, E.BIRTH_DATE, E.ID)) AS total
FROM EMPLOYEES E
WHERE E.BIRTH_DATE BETWEEN '2022-10-18 00:00:00' AND '2022-10-18 23:59:59'
  AND ((E.NAME IS NOT NULL AND E.LAST_NAME IS NOT NULL) OR (E.NAME IS NULL AND E.LAST_NAME IS NULL))
GROUP BY CASE 
           WHEN E.NAME IS NOT NULL AND E.LAST_NAME IS NOT NULL THEN 1 
           ELSE 2 
         END;

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