枢轴查询不起作用 [英] pivot query doesn't work
本文介绍了枢轴查询不起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有生以来第一次尝试在我的数据库表上使用 PIVOT.这是表:
I'm trying for the first time in my life to use the PIVOT on my database table. This is the table:
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);
但我正在努力做到这一点:
But I'm trying to get to this:
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 附近得到一个通用语法错误
with this query I get a generic syntax ERROR near 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
推荐答案
在 MySQL 中使用聚合的一种方法:
One way todo this in MySQL using aggregation:
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;
顺便说一下,把它放回同一张桌子是不合理的.查询应该足够了.您也可以将其放入视图或另一个表中.
By the way, it wouldn't be reasonable to put this back into the same table. The query should be sufficient. You can also put this into a view or into another table.
另一种方法是使用连接.查询有点复杂:
An alternative approach is to use joins. The query is a bit more complicated:
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';
这篇关于枢轴查询不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文