在我的查询结果中得到2列 [英] get 2 columns in my query result

问题描述

我有一个看起来像下面的表格

I have a table that looks like the following

PIN    QuestionNum     Response
1111   1               1
1111   2               3
2222   1               4
2222   2               3
3333   2               5

我的查询的预期输出为:

The expected output from my query would be:

PIN   Question1   Question2
1111  1           3
2222  4           3
3333  null        5

我不知道是否有可能使查询的输出看起来像这样.

I don't know if it is possible to make the output of a query appear like this.

任何人都可以请告知这是否可能.

Could anyone please advise if this is possible please.

推荐答案

这基本上是PIVOT，但是MySQL没有PIVOT功能.因此，您将要使用聚合函数和CASE语句复制此内容.如果知道您拥有的QuestionNum个值的数量，则可以对查询进行硬编码，如下所示:

This is basically a PIVOT but MySQL does not have PIVOT function. So you will want to replicate this using an aggregate function and a CASE statement. If you know the number of QuestionNum values that you have then you can hard-code the query similar to this:

select pin,
  max(case when QuestionNum = 1 then response end) Question1,
  max(case when QuestionNum = 2 then response end) Question2
from yourtable
group by pin

请参见带有演示的SQL提琴

现在，如果QuestionNum的值数量未知，则可以使用准备好的语句来生成此查询的动态版本:

Now, if you have an unknown number of values for QuestionNum, then you can use a prepared statement to generate a dynamic version of this query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when QuestionNum = ''',
      QuestionNum,
      ''' then response end) AS Question',
      QuestionNum
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT pin, ', @sql, ' 
                  FROM yourtable 
                  GROUP BY pin');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请参见带演示的SQL提琴

两者都会产生相同的结果:

Both will produce the same results:

|  PIN | QUESTION1 | QUESTION2 |
--------------------------------
| 1111 |         1 |         3 |
| 2222 |         4 |         3 |
| 3333 |    (null) |         5 |

这篇关于在我的查询结果中得到2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！