MySQL动态数据透视表 [英] MySQL dynamic pivot table
本文介绍了MySQL动态数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用具有动态列的数据透视表来工作.当user_id是字符串时,它可以正常工作,但是如果它是int,则它似乎会失败
Im trying to get a pivot table with dynamic columns to work. When user_id is a string, it works fine but if its an int, then it seems to fail
在过去的问题帮助下,到目前为止,这是我所拥有的:
Here is what I have so far with the assistance of past questions:
CREATE TABLE measure2
(`inspection_date` date, `user_id` int, `score` int, comment text)
;
INSERT INTO measure2
(`inspection_date`, `user_id`, `score`, comment)
VALUES
('2012-10-16', 0, 0, null),
('2012-10-16', 1, 0, null),
('2012-10-16', 2, 0, null),
('2012-10-16', 3, 0, null),
('2012-10-17', 0, 1, null),
('2012-10-17', 1, 1, null),
('2012-10-17', 2, 1, null),
('2012-10-18', 3, 1, null)
;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when user_id = ''',
user_id,
''' then score end) AS ',
user_id
)
) INTO @sql
FROM measure2;
SET @sql = CONCAT('SELECT inspection_date, ', @sql, '
FROM measure2
GROUP BY inspection_date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参阅: http://sqlfiddle.com/#!2/eab24/1
我确定它很简单,但是我想念的是什么?
Im sure its something simple, but what am I missing?
谢谢
推荐答案
由于值在int
中,因此您要将它们设为列名,因此必须将值包装在反引号中
Since the values are in int
you are are making them the column names, you have to wrap the values in a backtick
sql将如下所示:
max(case when user_id = 1 then score end) as `1`
完整查询将是:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when user_id = ''',
user_id,
''' then score end) AS `',
user_id, '`'
)
) INTO @sql
FROM measure2;
SET @sql = CONCAT('SELECT inspection_date, ', @sql, '
FROM measure2
GROUP BY inspection_date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见带演示的SQL提琴
这篇关于MySQL动态数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文