Oracle 11g双表关联多分组统计：按家庭类型统计FLAG计数

嘿，我来帮你搞定这个数据统计需求！咱们一步步拆解问题，最终实现你想要的输出格式。

核心思路 #

1. 准确关联两张表：通过IDG、IDE、IDD三个字段关联ED和EL表，确保每个成员的记录和对应的FLAG信息精准匹配。
2. 定义家庭组类型：用CASE语句把IDG+IDE的组合映射为你需要的组名称（Family、Couple、Dad-Child）。
3. 按组统计FLAG为TRUE的人数：针对每个FLAG字段，统计各组内满足条件的成员数量。
4. 转置输出格式：把统计结果从列结构转成行结构，匹配你期望的输出样式。

完整SQL代码 #

-- 输出与期望格式完全一致的统计结果
SELECT 'FLAG01=T: ' ||
       'Family: ' || MAX(CASE WHEN group_type='Family' THEN cnt END) ||
       ' Couple: ' || MAX(CASE WHEN group_type='Couple' THEN cnt END) ||
       ' Dad-Child: ' || MAX(CASE WHEN group_type='Dad-Child' THEN cnt END) AS result_line
FROM (
  SELECT 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END AS group_type,
    COUNT(CASE WHEN el.FLAG01 = TRUE THEN 1 END) AS cnt
  FROM ED ed
  JOIN EL el ON ed.IDG = el.IDG AND ed.IDE = el.IDE AND ed.IDD = el.IDD
  GROUP BY 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END
)
UNION ALL
SELECT 'FLAG02=T: ' ||
       'Family: ' || MAX(CASE WHEN group_type='Family' THEN cnt END) ||
       ' Couple: ' || MAX(CASE WHEN group_type='Couple' THEN cnt END) ||
       ' Dad-Child: ' || MAX(CASE WHEN group_type='Dad-Child' THEN cnt END) AS result_line
FROM (
  SELECT 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END AS group_type,
    COUNT(CASE WHEN el.FLAG02 = TRUE THEN 1 END) AS cnt
  FROM ED ed
  JOIN EL el ON ed.IDG = el.IDG AND ed.IDE = el.IDE AND ed.IDD = el.IDD
  GROUP BY 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END
)
UNION ALL
SELECT 'FLAG03=T: ' ||
       'Family: ' || MAX(CASE WHEN group_type='Family' THEN cnt END) ||
       ' Couple: ' || MAX(CASE WHEN group_type='Couple' THEN cnt END) ||
       ' Dad-Child: ' || MAX(CASE WHEN group_type='Dad-Child' THEN cnt END) AS result_line
FROM (
  SELECT 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END AS group_type,
    COUNT(CASE WHEN el.FLAG03 = TRUE THEN 1 END) AS cnt
  FROM ED ed
  JOIN EL el ON ed.IDG = el.IDG AND ed.IDE = el.IDE AND ed.IDD = el.IDD
  GROUP BY 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END
)
UNION ALL
SELECT 'FLAG04=T: ' ||
       'Family: ' || MAX(CASE WHEN group_type='Family' THEN cnt END) ||
       ' Couple: ' || MAX(CASE WHEN group_type='Couple' THEN cnt END) ||
       ' Dad-Child: ' || MAX(CASE WHEN group_type='Dad-Child' THEN cnt END) AS result_line
FROM (
  SELECT 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END AS group_type,
    COUNT(CASE WHEN el.FLAG04 = TRUE THEN 1 END) AS cnt
  FROM ED ed
  JOIN EL el ON ed.IDG = el.IDG AND ed.IDE = el.IDE AND ed.IDD = el.IDD
  GROUP BY 
    CASE
      WHEN ed.IDG = 12345 AND ed.IDE = 123 THEN 'Family'
      WHEN ed.IDG = 12345 AND ed.IDE = 321 THEN 'Couple'
      WHEN ed.IDG = 12345 AND ed.IDE = 555 THEN 'Dad-Child'
      ELSE 'Other'
    END
);

关键细节说明 #

1. 关联条件：使用IDG、IDE、IDD三个字段做JOIN，是因为每个成员在ED表中的IDD是组内唯一标识，对应EL表中同一组下的具体FLAG记录，避免出现错误匹配。
2. 组类型定义：CASE语句可以灵活扩展，如果后续新增其他家庭组类型，直接添加WHEN条件即可。
3. 统计逻辑：COUNT(CASE WHEN el.FLAGxx = TRUE THEN 1 END)的作用是只统计FLAG为TRUE的成员，CASE语句在条件不满足时返回NULL，COUNT会自动忽略NULL值，得到精准的统计数。
4. 格式转置：通过UNION ALL把每个FLAG的统计结果合并成多行，再用字符串拼接生成你需要的输出格式。

注意事项 #

如果EL表中的FLAG字段存储的是'Y'/'N'而非BOOLEAN类型，只需把代码中的= TRUE替换为= 'Y'即可。

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