mysql透视/交叉表查询

vmdwslir 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(259)

问题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   |

我知道我需要使用交叉表/数据透视查询来获得这个显示。为此，我尝试了它基于有限的知识，我对它。以下是我的疑问：

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

需要帮助。

sql mysql pivot

来源：https://stackoverflow.com/questions/53419143/left-join-11-times-in-the-same-table

3条答案

按热度按时间

vkc1a9a21#

尽管@john的static answer非常有效，但如果您有未知数量的列要转换，我会考虑使用prepared语句来获得结果：

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;

请参阅SQLFiddle演示
至于你的第二个，请澄清你是想做什么，这是不清楚的。

赞(0）回复(0）举报 2021-06-19

ldioqlga2#

@darkknightfan，这是一个非常有用的问题，对于我正在工作的任务。我继续修改了@bluefin的解决方案来解决你的第二个问题。下面的代码生成最初请求的格式，其中customer\u属性的值作为交叉表中的结果列标题。
相关的变化是：

' 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\u concat（）的默认最大长度为1024个字符。在你准备好的陈述的开头写上：

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

赞(0）回复(0）举报 2021-06-19

yvt65v4c3#

添加 GROUP_CONCAT 在你的 CASE 条款

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演示

赞(0）回复(0）举报 2021-06-19

我来回答

mysql透视/交叉表查询

3条答案

sqlfiddle演示

相关问题

热门标签

最新问答