如何在SQL中补全不存在的ID_EDAD_QUINQ与GENERO组合行并设total为0

当前分组查询仅返回存在数据的genero、id_edad_quinq、codigo_item组合行，需自动生成所有缺失的组合行，并将这些行的total列设为0。

解决方案思路 #

先生成所有可能的维度组合（genero、id_edad_quinq、codigo_item的笛卡尔积），再与原统计结果做左连接，将无匹配项的total值替换为0。

修改后的完整脚本 #

首先保留原临时表创建逻辑：

SELECT ID_CITA,
       FECHA_ATENCION,
       (case 
            when Tipo_Edad IN('M','D') THEN 1
            when  Edad_Reg BETWEEN 1 AND 4 AND Tipo_Edad='A'  THEN 2
            when  Edad_Reg BETWEEN 5 AND 9 AND Tipo_Edad='A'  THEN 3
            when  Edad_Reg BETWEEN 10 AND 14 AND Tipo_Edad='A'  THEN 4
            when  Edad_Reg BETWEEN 15 AND 19 AND Tipo_Edad='A'  THEN 5
            when  Edad_Reg BETWEEN 20 AND 24 AND Tipo_Edad='A'  THEN 6
            when  Edad_Reg BETWEEN 25 AND 29 AND Tipo_Edad='A'  THEN 7
            when  Edad_Reg BETWEEN 30 AND 34 AND Tipo_Edad='A' THEN 8
            when  Edad_Reg BETWEEN 35 AND 39 AND Tipo_Edad='A'  THEN 9
            when  Edad_Reg BETWEEN 40 AND 44 AND Tipo_Edad='A'  THEN 10
            when  Edad_Reg BETWEEN 45 AND 49 AND Tipo_Edad='A'  THEN 11
            when  Edad_Reg BETWEEN 50 AND 54 AND Tipo_Edad='A'  THEN 12
            when  Edad_Reg BETWEEN 55 AND 59 AND Tipo_Edad='A'  THEN 13
            when  Edad_Reg BETWEEN 60 AND 64 AND Tipo_Edad='A'  THEN 14
            when  Edad_Reg >=65 AND TIPO_EDAD='A' THEN 15
            ELSE 0
      END) AS ID_EDAD_QUINQ,
      (CASE WHEN TC.GENERO ='F' THEN 2 
            WHEN TC.Genero ='M' THEN 1
            ELSE 0
      END) AS GENERO,
      Descripcion_Item,
      Codigo_Item
INTO #casos_morbilidad
FROM BDHIS_MINSA.dbo.T_CONSOLIDADO_NUEVA_TRAMA_HISMINSA tc
WHERE Descripcion_Red like '%viru%' and mes=1 and concat(id_cita,'-',Id_Correlativo_Item) 
     IN (SELECT concat(id_cita,'-',Id_Correlativo_Item) 
     from BDHIS_MINSA.dbo.T_CONSOLIDADO_NUEVA_TRAMA_HISMINSA TC 
     WHERE LEFT(Codigo_Item,1) NOT IN ('Z','U'))
AND Tipo_Diagnostico='D'
AND Fg_Tipo='CX'

然后执行以下查询生成包含所有组合的统计结果：

-- 生成所有可能的genero值
WITH Generos AS (
    SELECT 1 AS GENERO UNION ALL
    SELECT 2 UNION ALL
    SELECT 0
),
-- 生成所有可能的id_edad_quinq值
EdadGrupos AS (
    SELECT 0 AS ID_EDAD_QUINQ UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
    SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
    SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
),
-- 获取所有涉及的codigo_item值
CodigosItem AS (
    SELECT DISTINCT Codigo_Item
    FROM #casos_morbilidad
),
-- 生成所有维度的笛卡尔积
TodasCombinaciones AS (
    SELECT g.GENERO, eg.ID_EDAD_QUINQ, ci.Codigo_Item
    FROM Generos g
    CROSS JOIN EdadGrupos eg
    CROSS JOIN CodigosItem ci
),
-- 原统计结果
EstadisticasOriginales AS (
    SELECT genero, id_edad_quinq, codigo_item, COUNT(id_cita) AS total
    FROM #casos_morbilidad
    GROUP BY genero, id_edad_quinq, codigo_item
)
-- 左连接补全缺失行，空值替换为0
SELECT tc.GENERO, tc.ID_EDAD_QUINQ, tc.Codigo_Item,
       ISNULL(eo.total, 0) AS total
FROM TodasCombinaciones tc
LEFT JOIN EstadisticasOriginales eo
    ON tc.GENERO = eo.genero
    AND tc.ID_EDAD_QUINQ = eo.id_edad_quinq
    AND tc.Codigo_Item = eo.codigo_item
ORDER BY tc.Codigo_Item, tc.GENERO, tc.ID_EDAD_QUINQ

说明 #

• Generos和EdadGruposCTE手动生成了所有可能的genero和id_edad_quinq值，确保覆盖所有逻辑上的可能值
• CodigosItem获取临时表中所有出现过的codigo_item，保证每个代码都有完整的组合
• TodasCombinaciones生成三者的全组合，确保没有遗漏
• 最后通过左连接将原统计结果与全组合关联，用ISNULL将无数据的total设为0

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