mysql行列转_mysql 行列转换

大致的语句就是这样：

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)