mysql - 将具有重复值的行转换为列 - MySQL

Question

我有一个表“A”，看起来像：

_______________________________________________________________
|query_id |   query      |  response   |user_response_count    |
|--------------------------------------------------------------- 
|   1     |   acne       |   BothBad   |       2               |
|   1     |   acne       |  BothGood   |       1               |
|   2     |   asthma     |   BothBad   |       1               |
|   2     |   asthma     |   product 1 |       1               |
|   2     |   asthma     |   BothGood  |       1               |
|   3     |   bell palsy |   product 2 |       2               |
|   3     |   bell palsy |   BothGood  |       1               |
 ---------------------------------------------------------------

我想编写一个查询来获得如下所示的内容：

__________________________________________________________________________________
| query_id |   query   |   BothGood   |   BothBad   |   Product 1 |   Product 2   |
-----------------------------------------------------------------------------------
|    1     |    acne   |         1    |    2        |       0     |         0     |
|    2     |   asthma  |         1    |    1        |       1     |         0     |
|    3     | bell palsy|         1    |    0        |       0     |         2     |
-----------------------------------------------------------------------------------

“user_response_count”列实际上是说，2 个用户为“acne”查询选择了“BothBad”选项。

我知道，通过使用max，我可以将行更改为列，但在这里很难做到最大。有什么想法吗？

标签： mysqlsql

Answer 1

条件聚合：

select query_id, query,
       sum(case when response = 'BothGood' then cnt else 0 end) as BothGood,
       sum(case when response = 'BothBad' then cnt else 0 end) as BothBad,
       sum(case when response = 'product 1' then cnt else 0 end) as product1,
       sum(case when response = 'product 2' then cnt else 0 end) as product2
from a
group by query_id, query;