mysql - 枢轴查询不起作用

Question

这个问题已经有答案了:

How can I return pivot table output in MySQL? (10 个回答)

已关闭 9 年前。

这是我一生中第一次尝试在数据库表上使用 PIVOT。这是表格:

Answers (id_user, id_question, topic, value) VALUES
(1, 1, 'a', 3),
(1, 1, 'b', 3),
(1, 1, 'c', 2),

(1, 2, 'a', 1),
(1, 2, 'b', 2),
(1, 2, 'c', 3),

[...]

(1, 14, 'a', 1),
(1, 14, 'b', 2),
(1, 14, 'c', 1);

但我正在努力做到这一点:

Answers (id_user, id_question, topic_A, topic_B, topic_C) VALUES
(1, 1, 3, 3, 2),
(1, 2, 1, 2, 3),
[...]
(1, 14, 1, 2, 1);

通过这个查询，我在 PIVOT 附近得到一个通用语法错误

SELECT *
FROM
(     SELECT id_user, id_question, topic, value
      FROM Answers
      WHERE id_user=98
) as risp
PIVOT 
(     FOR id_question IN ([a],[b],[c])
)     AS pvt

Answer 1

在 MySQL 中使用聚合实现此目的的一种方法:

select id_user, id_question,
       max(case when topic = 'A' then value end) as topic_A,
       max(case when topic = 'B' then value end) as topic_B,
       max(case when topic = 'C' then value end) as topic_C
from Answers a
group by id_user, id_question;

顺便说一句，将其放回同一个表中是不合理的。查询应该足够了。您还可以将其放入 View 或另一个表中。

编辑:

另一种方法是使用联接。查询有点复杂:

select uq.id_user, uq.id_question, aa.value as topic_A,
       ab.value as topic_B, ac.value as topic_C
from (select distinct id_user, id_question from answers) uq left outer join
     Answers aa
     on aa.id_user = uq.id_user and aa.id_question = uq.id_question and
        aa.topic = 'A' left outer join
     Answers ab
     on ab.id_user = uq.id_user and ab.id_question = uq.id_question and
        ab.topic = 'B' left outer join
     Answers aa
     on ac.id_user = uq.id_user and ac.id_question = uq.id_question and
        ac.topic = 'C';