将行转置为 MYSQL 中的标题
我正在尝试转置一个非常基本的输出以用于 Web 应用程序.由于我的优势更多在于 mysql,所以我想在它进入应用程序之前这样做.
I'm trying to transpose a very basic output for use in a web application. As my strengths lie moreso in mysql, I'd like to this before it hits the app.
我目前拥有的是:
date value
2012-01-01 23
2012-01-02 33
2012-01-03 56
2012-01-04 10
我想要的是:
2012-01-01 2012-01-02 2012-01-03 2012-01-04
23 33 56 10
我的sql是:
SELECT
date,
value
from values
where date >= curdate() - interval 3 day
我已经在网上进行了大量研究,但我想不出一个好的方法来做到这一点.这需要是动态的,因为日期确实每天都在变化.
I've done a ton of research online and I can't figure out a good way to do this. This would need to be dynamic as the dates do change daily.
推荐答案
这是一个动态 sql 来透视记录,
Here's a dynamic sql to pivot records,
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN date = ''',
date,
''' THEN Value ELSE NULL END) AS ',
CONCAT('`', date, '`')
)) INTO @sql
FROM TableName
// WHERE date >= curdate() - interval 3 day // add condition here
ORDER BY date;
SET @sql = CONCAT('SELECT ', @sql, '
FROM TableName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- SQLFiddle 演示
相关文章