mysql 行列转换

A 表
id          time       date
1        7:00     20160105
1        8:00     20160105
1        7:00     20160104
2        7:00     20160104
转换后的期望结果
id     time1   time2    date
1        7:00                  20160104
2        7:00                  20160104
1       7:00    8:00       20160105
可能会有Time3列
求SQL 文啊高手

解决方案

修改成下面语句好点

select id,
			 case when SUBSTR(a,1,4)="7:00"then SUBSTR(a,1,4) end time1,
			 case when SUBSTR(a,1,4)="8:00"then SUBSTR(a,1,4) 
				    when SUBSTR(a,5,4)="8:00"then SUBSTR(a,5,4) end time2,
			 case when SUBSTR(a,1,4)="9:00"then SUBSTR(a,1,4) 
				    when SUBSTR(a,5,4)="9:00"then SUBSTR(a,5,4)
				    when SUBSTR(a,9,4)="9:00"then SUBSTR(a,9,4) end time3,
			 date
  from (select id,date,
							 replace(GROUP_CONCAT(time),",","") as a 
				  from t_time a 
			   group by id,date
       ) b;

大致的语句就是这样：

set @sql = "";
select @sql := concat(@sql,",max(case when time = """,time,""" then time end)") from (select distinct time from t_time)t;
set @sql = concat("select id",@sql,",date from t_time group by id,date");
prepare stmt from @sql;
execute stmt;

运行结果：

mysql> set @sql = "";
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select @sql := concat(@sql,",max(case when time = """,time,""" then time end)") from (select distinct time from t_time)t;
+--+
| @sql := concat(@sql,",max(case when time = """,time,""" then time end)")                                                          |
+--+
| ,max(case when time = "7:00" then time end)                                                                                       |
| ,max(case when time = "7:00" then time end),max(case when time = "8:00" then time end)                                            |
| ,max(case when time = "7:00" then time end),max(case when time = "8:00" then time end),max(case when time = "9:00" then time end) |
+--+
3 rows in set (0.03 sec)
mysql>
mysql> set @sql = concat("select id",@sql,",date from t_time group by id,date");
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql>
mysql> execute stmt;
+--+--+--+--+--+
| id   | max(case when time = "7:00" then time end) | max(case when time = "8:00" then time end) | max(case when time = "9:00" then time end) | date     |
+--+--+--+--+--+
|    1 | 7:00                                       | NULL                                       | NULL                                       | 20160104 |
|    1 | 7:00                                       | 8:00                                       | NULL                                       | 20160105 |
|    2 | 7:00                                       | NULL                                       | 9:00                                       | 20160104 |
|    3 | 7:00                                       | 8:00                                       | 9:00                                       | 20160104 |
+--+--+--+--+--+
4 rows in set (0.00 sec)