MySQL 旋转没有按预期工作

Question

架构:

DROP TABLE IF EXISTS `questions_tags`;
CREATE TABLE `questions_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `questions_tags` VALUES ('1', '1', '1');
INSERT INTO `questions_tags` VALUES ('2', '2', '1');
INSERT INTO `questions_tags` VALUES ('3', '3', '1');
INSERT INTO `questions_tags` VALUES ('4', '4', '1');
INSERT INTO `questions_tags` VALUES ('5', '5', '1');
INSERT INTO `questions_tags` VALUES ('6', '2', '2');

数据:

id  tag_id  question_id
1     1         1
2     2         1
3     3         1
4     4         1
5     5         1
6     2         2

我尝试过的:

SELECT 
question_id,
CASE WHEN tag_id = 1 THEN 'TAG1' END AS FirstTag,
CASE WHEN tag_id = 2 THEN 'TAG2' END AS SecondTag,
CASE WHEN tag_id = 3 THEN 'TAG3' END AS ThirdTag,
CASE WHEN tag_id = 4 THEN 'TAG4' END AS FourthTag,
CASE WHEN tag_id = 5 THEN 'TAG5' END AS FifthTag
FROM questions_tags
GROUP BY question_id;

当前输出:

预期输出:

我对 Pivoting 有什么错误的判断吗？任何帮助表示赞赏。

Answer 1

对案例表达式使用聚合。

SELECT 
question_id,
max(CASE WHEN tag_id = 1 THEN 'TAG1' END) AS FirstTag,
max(CASE WHEN tag_id = 2 THEN 'TAG2' END) AS SecondTag,
max(CASE WHEN tag_id = 3 THEN 'TAG3' END) AS ThirdTag,
max(CASE WHEN tag_id = 4 THEN 'TAG4' END) AS FourthTag,
max(CASE WHEN tag_id = 5 THEN 'TAG5' END) AS FifthTag
FROM questions_tags
GROUP BY question_id;