SQL查询需求：统计Consumer表中Gender与Status组合的对应计数

现有Consumer表，结构及数据如下：

期望得到的统计结果：

尝试的SQL语句：

SELECT 
    t3.Gender, t3.Status, COUNT(*) AS count 
FROM
    (SELECT DISTINCT
         t1.Gender, t1.Status 
     FROM 
         Consumer AS t1
     CROSS JOIN 
         Consumer AS t2 
     WHERE 
         t1.Status <> t2.Status 
         OR t1.Status <> t2.Status) AS t3 
GROUP BY
    t3.Gender, t3.Status

但未得到预期结果，求正确的SQL查询方案。

你的原SQL使用了不必要的CROSS JOIN，逻辑上无法覆盖Unknown性别且Status为空的统计需求，且重复的OR条件没有实际意义。以下是两种可行的实现方案：

方法1：基于CTE的左连接补全法 #

WITH unique_genders AS (
    SELECT DISTINCT Gender FROM Consumer
),
valid_status_counts AS (
    SELECT 
        Gender, 
        Status, 
        COUNT(*) AS Count
    FROM Consumer
    WHERE Status IS NOT NULL AND Status <> ''
    GROUP BY Gender, Status
)
SELECT 
    ug.Gender,
    vsc.Status,
    COALESCE(vsc.Count, 0) AS Count
FROM unique_genders ug
LEFT JOIN valid_status_counts vsc ON ug.Gender = vsc.Gender
ORDER BY 
    CASE ug.Gender 
        WHEN 'Male' THEN 1 
        WHEN 'Female' THEN 2 
        WHEN 'Unknown' THEN 3 
    END;

方法2：分组+UNION补全行法 #

SELECT 
    Gender,
    Status,
    COUNT(ID) AS Count
FROM Consumer
WHERE Status IS NOT NULL AND Status <> ''
GROUP BY Gender, Status
UNION ALL
SELECT 'Unknown', '', 0
WHERE NOT EXISTS (
    SELECT 1 FROM Consumer WHERE Gender = 'Unknown' AND Status IS NOT NULL AND Status <> ''
)
ORDER BY 
    CASE Gender 
        WHEN 'Male' THEN 1 
        WHEN 'Female' THEN 2 
        WHEN 'Unknown' THEN 3 
    END;

核心逻辑说明 #

1. 先过滤掉Status为空的无效行，统计各Gender对应有效Status的数量
2. 通过左连接或UNION ALL补全Unknown性别无有效Status的行，用COALESCE或直接赋值0确保统计值正确
3. 最后的ORDER BY用于对齐你期望的结果顺序，可根据实际需求调整

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