在一个特定的方式在 MySQL 数据透视表

标签: sql MySQL PHP
发布时间: 2017/4/9 23:12:50
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

还有其他数据透视表问题,但我似乎无法找到我的问题的答案。

这是我的表

ID    QUESTION       ANSWER       RECORDID      SORTORDER
1     Question 1     Answer 1.1   123456        1
2     Question 2     Answer 2.1   123456        2
3     Question 3     Answer 3.1   123456        3
4     Question 1     Answer 1.2   654321        1
5     Question 2     Answer 2.2   654321        2
6     Question 3     Answer 3.2   654321        3

等。

我想与一个查询,以将其输出︰

Question 1    Question 2    Question 3
Answer 1.1    Answer 2.1    Answer 3.1
Answer 1.2    Answer 2.2    Answer 3.2

因此,简言之,问题列保存不是唯一的问题 (在此演示中有 2 个条目 (见 RECORDID)) 所以他们需要进行分组和排序的排序次序。RECORDID 始终是唯一

我见过一些枢轴逆透视的例子,但不能得到我的头在它附近。 有人可以帮我吗?

解决方法 1:

这会管用。在内部查询你得到只有答案为特定问题和外部查询中分组的 recordid 你摆脱空值时......请尝试运行仅内部查询得到的想法。

select  
  max(q.Question1),
  max(q.Question2),
  max(q.Question3) FROM (SELECT     
                         recordid,
                         case when question = 'Question 1' then answer else null end as Question1, 
                         case when question = 'Question 2' then answer else null end as Question2, 
                         case when question = 'Question 3' then answer else null end as Question3 FROM questions) q group by q.recordid

让我知道是否你有任何论文答辩 !

编辑︰ 您的评论将添加另一个级别的复杂性:)现在我看到我们如何可以使用该列排序顺序...我们需要一个更内在的查询来获取其排序顺序和问题。然后得到的答案每个排序顺序,然后分组的记录的 id 筛选出空值并得到期望的结果。请试试这,让我知道它如何去...Select 语句中的问题数目必须等于具有大多数问题的形式问题数目-我投入了 8 只是为了告诉你,这不应该限制你。现在你不会依赖问题名称-只是排序编号。它是好的你有的排序顺序 — — 否则,您可能要生成每个每个记录的 id 的行号......

SET @rank=0; SET @id:=0; select recordid, max(qq.question1) as 'Question 1', max(qq.question2) as 'Question 2', max(qq.question3) as 'Question 3', max(qq.question4) as 'Question 4', max(qq.question5) as 'Question 5', max(qq.question6) as 'Question 6', max(qq.question7) as 'Question 7', max(qq.question8) as 'Question 8' FROM ( SELECT recordid, case when q.rownumber = 1 then CONCAT(question,': ', answer) else null end as question1, case when q.rownumber = 2 then CONCAT(question,': ', answer) else null end as question2, case when q.rownumber = 3 then CONCAT(question,': ', answer) else null end as question3, case when q.rownumber = 4 then CONCAT(question,': ', answer) else null end as question4, case when q.rownumber = 5 then CONCAT(question,': ', answer) else null end as question5, case when q.rownumber = 6 then CONCAT(question,': ', answer) else null end as question6, case when q.rownumber = 7 then CONCAT(question,': ', answer) else null end as question7, case when q.rownumber = 8 then CONCAT(question,': ', answer) else null end as question8 FROM( select recordid, question, answer, sortorder, @rank:=CASE WHEN @id=recordid THEN @rank+1 ELSE 1 END as rownumber, @id:=recordid from questions order by recordid, sortorder ) q )qq GROUP BY recordid

官方微信
官方QQ群
31647020