MySQL SELECT查询补全部门所有状态0值 临时表/CTE实现求助

核心实现逻辑是先生成全量部门+全量状态的维度组合，再左关联实际业务统计结果，未匹配到的统计值自动补0即可，无需手动创建临时表更新。以下是两种常用实现方式：

方式1：MySQL 8.0+ 支持的CTE写法（你提到的公用表表达式） #

WITH 
-- 先拿到所有不重复的部门
all_departments AS (
    SELECT DISTINCT Departament FROM ur_tasks_details
),
-- 拿到所有不重复的工单状态（固定4种也可以直接枚举，更精准）
all_statuses AS (
    SELECT DISTINCT statusDef AS statusoforder FROM ur_tasks_status
),
-- 生成部门和状态的全量笛卡尔积组合
full_dimension AS (
    SELECT ad.Departament, ast.statusoforder
    FROM all_departments ad
    CROSS JOIN all_statuses ast
),
-- 原有统计逻辑，过滤月份的条件放在这里
actual_stats AS (
    SELECT 
        utd.Departament,
        uts.statusDef AS statusoforder,
        COUNT(uts.statusDef) AS Ilosc_Statusow 
    FROM ur_tasks_details utd 
    INNER JOIN ur_tasks_status uts ON utd.StatusOfOrder = uts.statusNR 
    WHERE utd.month = 'Sierpien' 
    GROUP BY uts.statusDef, utd.Departament
)
-- 关联全量维度和统计结果，空值补0
SELECT 
    fd.Departament,
    fd.statusoforder,
    IFNULL(ast.Ilosc_Statusow, 0) AS Ilosc_Statusow
FROM full_dimension fd
LEFT JOIN actual_stats ast 
    ON fd.Departament = ast.Departament 
    AND fd.statusoforder = ast.statusoforder
ORDER BY fd.Departament, fd.statusoforder;

方式2：兼容低版本MySQL的子查询写法 #

如果你的MySQL版本不支持CTE，直接把子查询嵌套进去即可，逻辑和上面完全一致：

SELECT 
    fd.Departament,
    fd.statusoforder,
    IFNULL(ast.Ilosc_Statusow, 0) AS Ilosc_Statusow
FROM (
    -- 全量维度组合
    SELECT ad.Departament, ast.statusoforder
    FROM (SELECT DISTINCT Departament FROM ur_tasks_details) ad
    CROSS JOIN (SELECT DISTINCT statusDef AS statusoforder FROM ur_tasks_status) ast
) fd
LEFT JOIN (
    -- 实际统计结果
    SELECT 
        utd.Departament,
        uts.statusDef AS statusoforder,
        COUNT(uts.statusDef) AS Ilosc_Statusow 
    FROM ur_tasks_details utd 
    INNER JOIN ur_tasks_status uts ON utd.StatusOfOrder = uts.statusNR 
    WHERE utd.month = 'Sierpien' 
    GROUP BY uts.statusDef, utd.Departament
) ast ON fd.Departament = ast.Departament AND fd.statusoforder = ast.statusoforder
ORDER BY fd.Departament, fd.statusoforder;

注意事项 #

• 如果你确认每个部门固定对应4种状态，也可以直接枚举状态值替换all_statuses部分的逻辑，避免状态表存在冗余状态导致多余行：

  SELECT '状态1' statusoforder UNION ALL
  SELECT '状态2' statusoforder UNION ALL
  SELECT '状态3' statusoforder UNION ALL
  SELECT '状态4' statusoforder
  

• 月份过滤条件必须放在实际统计的子查询内部，不能写到最外层的WHERE中，否则会过滤掉左关联生成的空值行，达不到补0的效果。

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