mysql 数据透视表/交叉表查询

mysql pivot/crosstab query(mysql 数据透视表/交叉表查询)
本文介绍了mysql 数据透视表/交叉表查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

问题 1:我有一个包含以下结构和数据的表格:

Question 1: I have a table with the below structure and data:

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                             

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

I want to display these records in the below format:

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   |

我知道我需要使用交叉表/枢轴查询来获得此显示.为此,我根据我对它的有限知识进行了尝试.以下是我的查询:

I know I need to use crosstab/pivot query to get this display. For this I tried it based on the limited knowledge that I have about it. Following is my query:

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

基于这个查询,我得到了以下显示:

And based on this query I got the below display:

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            

任何人都可以帮助我进行适当的更改,我需要对我的查询进行适当的更改,以获取单行中的记录,而不是上面的多行.

Can anyone help me with the proper changes that I need to make to my query to get the records in one single row and not multiple rows as above.

问题 2: 还有一种方法可以获取特定字段的值作为列的名称.正如你在上面看到的,我有 user_input1, user_input2,... 作为标题.相反,我希望将 customer_attribute 中的值作为列的标题.

Question 2: Also is there a way to get the value of a particular field as the NAME of the column. As you can see above I have user_input1, user_input2,... as the header. Instead of that I want to have the values in customer_attribute as the header of the columns.

为此,我检查了 NAME_CONST(name,value) 如下:

For this I checked NAME_CONST(name,value) as below:

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

需要帮助.

推荐答案

虽然@John 的静态答案效果很好,但如果您要转换的列数未知,我会考虑使用准备好的语句来获得结果:

While @John's static answer works great, if you have an unknown number of columns that you want to transform, I would consider using prepared statements to get the results:

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 with Demo

至于您的第二个,请澄清您正在尝试做的事情尚不清楚.

As far as your second, please clarify what you are trying to do it is not clear.

这篇关于mysql 数据透视表/交叉表查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)