mysql如何将group by的行数据转为列

Question

原始数据表如下

id	name	money	time
1	mike	6	2016-09-01 12:58:00
2	mike	10	2016-09-01 13:52:56
3	leo	10	2016-09-02 00:05:05
4	mike	6	2016-09-03 08:06:05

希望转制后的数据表如下

name	2016-09-01	2016-09-02	2016-09-03
mike	16	0	6
leo	0	10	0

以demo为例，可以说说这类问题的解决思路吗？比如统计两个月的数据，那么就会有60行转为60列，是否存在效率的瓶颈

Answer 1

select sum(money) s,name,date_format(time,'%Y-%m-%d') d from table group by name,d;
然后根据这里转换

Answer 2

非要用sql做的话，得写存储过程，然后创建个临时表了。
没必要，把数据取出来后用服务端去做吧。效率高很多。

Answer 3

create table tt1(id int ,name VARCHAR(100),money FLOAT,time DATETIME)
insert into tt1
select 1,'mike',6,'2016-09-01 12:58:00' UNION
select 2,'mike',10,'2016-09-01 13:52:56' UNION
select 3,'leo',10,'2016-09-02 00:05:05' UNION
select 4,'mike',6,'2016-09-03 08:06:05'

select name,sum(case when datediff(time,'2016-09-01')=0 then money else 0 end) as '2016-09-01'
      ,sum(case when datediff(time,'2016-09-02')=0 then money else 0 end) as '2016-09-02'
      ,sum(case when datediff(time,'2016-09-03')=0 then money else 0 end) as '2016-09-03'
from tt1 GROUP BY  name

Answer 4

@ch21st 给的就是典型的行转列SQL写法
因为只需要表扫描一次，因此一般情况下不存在性能问题，除非是特别大的表。

这种写法本身只是一个解决思路，如果列不固定的话，可以在应用端通过php或java动态生成sql语句。

Answer 5

写个生成脚本的sql就完了嘛

select "select name," 
union 
select concat("sum(case when datediff(time,'",date(time),"')=0 then money else 0 end) as '",date(time),"',") a from tt1 group by a 
union 
select " from tt1 group by name;";

PS：union中间段最后一个逗号记得去掉

Answer 6

SELECT 
    name, 
    sum( if(date_format(time,'%Y-%m-%d') = '2016-09-01', money, 0 ) ) AS '2016-09-01',  
    sum( if(date_format(time,'%Y-%m-%d') = '2016-09-02', money, 0 ) ) AS '2016-09-02',  
    sum( if(date_format(time,'%Y-%m-%d') = '2016-09-03', money, 0 ) ) AS '2016-09-03'
FROM 
    money_table 
GROUP BY 
    name;