如何对多表关联、使用SUM CASE WHEN的查询结果执行unpivot逆透视操作

解决方案 #

方案1：直接分组统计（最优） #

你不需要先聚合为多列再做行转列，直接按opcao字段分组计数即可，性能更高逻辑更简洁：

SELECT
  cpo.opcao AS `OPTION`,
  COUNT(*) AS `VALUE`
FROM
  paciente_paciente pp
  INNER JOIN coleta_preenchimento cp ON cp.paciente_id = pp.paciente_id
  INNER JOIN coleta_preenchimento_pergunta cpp ON cpp.preenchimento_id = cp.preenchimento_id
  INNER JOIN coleta_pergunta_opcao cpo ON cpo.opcao_id = cpp.opcao_id
  INNER JOIN coleta_atendimento_formulario caf ON caf.preenchimento_id = cp.preenchimento_id
  INNER JOIN coleta_atendimento ca ON ca.atendimento_id = caf.atendimento_id
WHERE
  ca.status = 'FINALIZADO'
  AND cpp.pergunta_id = 1076
  AND cpo.opcao IN ('ABC','DEF','GHI','JKL','MNO') -- 限定需要统计的选项范围
GROUP BY
  cpo.opcao
ORDER BY
  cpo.opcao;

方案2：基于现有聚合结果做行转列 #

如果你的业务逻辑必须先输出单行多列的统计结果再转成行格式，可以用对应数据库的行转列语法实现：

MySQL 8.0+ 写法

WITH agg_result AS (
  -- 此处为你原有的聚合逻辑，已删除末尾多余逗号
  SELECT
    SUM(CASE WHEN opcao = 'ABC' THEN 1 ELSE 0 END) as ABC,
    SUM(CASE WHEN opcao = 'DEF' THEN 1 ELSE 0 END) as DEF,
    SUM(CASE WHEN opcao = 'GHI' THEN 1 ELSE 0 END) as GHI,
    SUM(CASE WHEN opcao = 'JKL' THEN 1 ELSE 0 END) as JKL,
    SUM(CASE WHEN opcao = 'MNO' THEN 1 ELSE 0 END) as MNO
  FROM
    paciente_paciente pp
    INNER JOIN coleta_preenchimento cp on cp.paciente_id = pp.paciente_id
    INNER JOIN coleta_preenchimento_pergunta cpp on cpp.preenchimento_id = cp.preenchimento_id
    INNER JOIN coleta_pergunta_opcao cpo  on cpo.opcao_id = cpp.opcao_id
    INNER JOIN coleta_atendimento_formulario caf on caf.preenchimento_id = cp.preenchimento_id
    INNER JOIN coleta_atendimento ca on ca.atendimento_id = caf.atendimento_id
  WHERE
    ca.status = 'FINALIZADO'
    AND cpp.pergunta_id= 1076
)
SELECT
  v.`OPTION`,
  v.`VALUE`
FROM agg_result
CROSS JOIN (
  VALUES
    ROW('ABC', ABC),
    ROW('DEF', DEF),
    ROW('GHI', GHI),
    ROW('JKL', JKL),
    ROW('MNO', MNO)
) AS v(`OPTION`, `VALUE`);

SQL Server 写法

WITH agg_result AS (
  -- 此处为你原有的聚合逻辑，已删除末尾多余逗号
  SELECT
    SUM(CASE WHEN opcao = 'ABC' THEN 1 ELSE 0 END) as ABC,
    SUM(CASE WHEN opcao = 'DEF' THEN 1 ELSE 0 END) as DEF,
    SUM(CASE WHEN opcao = 'GHI' THEN 1 ELSE 0 END) as GHI,
    SUM(CASE WHEN opcao = 'JKL' THEN 1 ELSE 0 END) as JKL,
    SUM(CASE WHEN opcao = 'MNO' THEN 1 ELSE 0 END) as MNO
  FROM
    paciente_paciente pp
    INNER JOIN coleta_preenchimento cp on cp.paciente_id = pp.paciente_id
    INNER JOIN coleta_preenchimento_pergunta cpp on cpp.preenchimento_id = cp.preenchimento_id
    INNER JOIN coleta_pergunta_opcao cpo  on cpo.opcao_id = cpp.opcao_id
    INNER JOIN coleta_atendimento_formulario caf on caf.preenchimento_id = cp.preenchimento_id
    INNER JOIN coleta_atendimento ca on ca.atendimento_id = caf.atendimento_id
  WHERE
    ca.status = 'FINALIZADO'
    AND cpp.pergunta_id= 1076
)
SELECT [OPTION], [VALUE]
FROM agg_result
UNPIVOT (
  [VALUE] FOR [OPTION] IN (ABC, DEF, GHI, JKL, MNO)
) AS unpvt;

注意事项 #

你原查询中MNO列的定义末尾多了一个多余的逗号，直接运行会触发语法错误，需要先删除。

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