SQL实现所有组合匹配计数查询（禁用CTE和PIVOT）

现有Persons表如下：

需要输出如下结果，展示Gender与StatusValue的所有可能组合及对应匹配计数，且不能使用CTE和PIVOT：

尝试的SQL语句计数结果错误：

SELECT Gender,StatusValue,Count(*) as Count FROM Persons 
WHERE Gender <> '' AND StatusValue <> '' 
GROUP BY Gender,StatusValue 
UNION
SELECT 
CASE WHEN G.Gender <> '' THEN G.Gender ELSE 'Empty' END,
CASE WHEN T.StatusValue <> '' THEN T.StatusValue ELSE 'Empty' END, 0 as COUNT from
(SELECT DISTINCT Gender FROM Persons) as G, (SELECT DISTINCT StatusValue FROM Persons) as T

SELECT 
    ROW_NUMBER() OVER (ORDER BY Gender, StatusValue) AS Id,
    G.Gender,
    S.StatusValue,
    COALESCE(P.Count, 0) AS Count
FROM 
    (SELECT DISTINCT Gender FROM Persons) G
CROSS JOIN 
    (SELECT 'SV001' AS StatusValue UNION SELECT 'SV002' UNION SELECT 'Empty') S
LEFT JOIN 
    (SELECT 
         Gender,
         CASE WHEN StatusValue = '' THEN 'Empty' ELSE StatusValue END AS StatusValue,
         COUNT(*) AS Count
     FROM Persons
     GROUP BY Gender, CASE WHEN StatusValue = '' THEN 'Empty' ELSE StatusValue END) P
ON 
    G.Gender = P.Gender AND S.StatusValue = P.StatusValue
ORDER BY 
    Id;

方案说明 #

1. 生成全量组合：通过CROSS JOIN将所有不同的Gender与预设的StatusValue选项（SV001、SV002、Empty）做笛卡尔积，确保覆盖所有可能的配对。
2. 统计原表数据：子查询P先将原表中空的StatusValue转换为'Empty'，再按Gender和处理后的StatusValue分组统计实际匹配数量。
3. 补全空计数：使用LEFT JOIN关联全量组合与统计结果，通过COALESCE将未匹配到的统计值（null）转换为0。
4. 生成自增ID：利用ROW_NUMBER()函数生成结果中的自增Id，按Gender和StatusValue排序保证顺序一致。

原SQL错误原因 #

• UNION操作会导致重复组合（原表已存在的组合会被统计两次：一次来自GROUP BY，一次来自笛卡尔积的0计数）。
• WHERE条件过滤掉了空StatusValue的行，导致统计不全。
• 未统一将空StatusValue转换为'Empty'，无法与目标结果的分类对齐。

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