MySQL 透视/交叉表查询

Question

MySQL 透视/交叉表查询

14

问题1： 我有一个如下所示的结构和数据的表：

app_id  transaction_id  mobile_no   node_id  customer_attribute  entered_value 
100     111             9999999999  1        Q1                  2                             
100     111             9999999999  2        Q2                  1                             
100     111             9999999999  3        Q3                  4                             
100     111             9999999999  4        Q4                  3                             
100     111             9999999999  5        Q5                  2                             
100     222             8888888888  4        Q4                  1                             
100     222             8888888888  3        Q3                  2                             
100     222             8888888888  2        Q2                  1                             
100     222             8888888888  1        Q1                  3                             
100     222             8888888888  5        Q5                  4

我想以以下格式显示这些记录：

app_id  |  transaction_id  | mobile     |  Q1  |  Q2  |  Q3  |  Q4 |  Q5  |
 100    |      111         | 9999999999 |   2  |   1  |   4  |  3  |  2   |
 100    |      222         | 8888888888 |   3  |   1  |   2  |  1  |  4   |

我知道我需要使用crosstab / pivot查询来获得此显示。为此，我根据自己有限的知识尝试了它。以下是我的查询：

SELECT app_id, transaction_id, mobile_no,
  (CASE node_id WHEN 1 THEN entered_value ELSE '' END) AS user_input1,
  (CASE node_id WHEN 2 THEN entered_value ELSE '' END) AS user_input2,
  (CASE node_id WHEN 3 THEN entered_value ELSE '' END) AS user_input3,
  (CASE node_id WHEN 4 THEN entered_value ELSE '' END) AS user_input4,
  (CASE node_id WHEN 5 THEN entered_value ELSE '' END) AS user_input5
FROM trn_user_log 
GROUP BY app_id, transaction_id, mobile_no, node_id

根据这个查询，我得到了如下显示：

app_id  transaction_id  mobile_no   user_input1  user_input2  user_input3  user_input4  user_input5  
100     111             9999999999  2                                                                
100     111             9999999999               1                                                   
100     111             9999999999                            4                                      
100     111             9999999999                                         3                         
100     111             9999999999                                                      2            
100     222             8888888888  3                                                                
100     222             8888888888               1                                                   
100     222             8888888888                            2                                      
100     222             8888888888                                         1                         
100     222             8888888888                                                      4

请问有人能帮我更改查询语句以便将记录放在一行中而不是多行吗？

问题2：同时，有没有一种方法可以将特定字段的值作为列名。如上所示，我的表头为user_input1、user_input2等。我想要的是将customer_attribute的值作为列头。

为此，我查看了以下内容：NAME_CONST(name,value)：

SELECT app_id, transaction_id, mobile_no,
NAME_CONST(customer_attribute, (CASE node_id WHEN 1 THEN entered_value ELSE '' END))
FROM trn_user_log

但是它会出现错误

Error Code : 1210 Incorrect arguments to NAME_CONST

需要帮助。

- DarkKnightFan

3个回答

9

在你的CASE语句中添加GROUP_CONCAT函数。

SELECT app_id, transaction_id, mobile_no,
  GROUP_CONCAT((CASE node_id WHEN 1 THEN entered_value ELSE NULL END)) AS user_input1,
  GROUP_CONCAT((CASE node_id WHEN 2 THEN entered_value ELSE NULL END)) AS user_input2,
  GROUP_CONCAT((CASE node_id WHEN 3 THEN entered_value ELSE NULL END)) AS user_input3,
  GROUP_CONCAT((CASE node_id WHEN 4 THEN entered_value ELSE NULL END)) AS user_input4,
  GROUP_CONCAT((CASE node_id WHEN 5 THEN entered_value ELSE NULL END)) AS user_input5
FROM trn_user_log 
GROUP BY app_id, transaction_id, mobile_no

SQLFiddle演示

- John Woo

@Gaurav，为什么在 SQLFiddle 中可以工作？请在你的 group by 子句中移除 node_id。 - John Woo

太好了！现在它可以用了。忘记从groupby中移除node_id了。你有我第二个问题的答案吗？能否显示customer_attribute的值代替user_inputx？ - DarkKnightFan

@Gaurav 抱歉，我无法帮助你解决这个问题，因为你似乎将记录转换为列名。 - John Woo

3

@DarkKnightFan，这是一个非常有用的问题，与我正在处理的任务相关。我已经修改了@bluefin的解决方案来解决您的第二个问题。以下代码将以customer_attribute的值作为交叉表中的结果列标题生成您最初请求的格式。

相关更改是更改为：

' then entered_value else NULL END)) AS user_input',
      node_id

转化为：

' then entered_value else NULL END)) AS ''',
          customer_attribute,''''

完整代码如下：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE node_id when ',
      node_id,
      ' then entered_value else NULL END)) AS ''',
      customer_attribute,''''
    )
  ) INTO @sql
FROM trn_user_log;


SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                  FROM trn_user_log 
                  GROUP BY app_id, transaction_id, mobile_no');

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

此外，对于浏览此问题的其他用户，如果您正在尝试交叉表多个值，则可能会遇到错误，因为GROUP_CONCAT（）的默认最大长度为1024个字符。要增加此长度，请在准备好的语句开头添加以下内容：

SET SESSION group_concat_max_len = value; -- replace value with an int

- Kolath

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

虽然 @John 的静态答案非常好用，但如果你有一个不确定的列数需要进行变换，我建议考虑使用预处理语句来获取结果：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE node_id when ',
      node_id,
      ' then entered_value else NULL END)) AS user_input',
      node_id
    )
  ) INTO @sql
FROM trn_user_log;


SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                  FROM trn_user_log 
                  GROUP BY app_id, transaction_id, mobile_no');

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

请参见带演示的 SQL Fiddle

关于您的第二个问题，请澄清您试图做什么，目前不是很清楚。