mysql行转列（日期）

[rushm]

CREATE TABLE `tet` (
`id` int(10) not NULL AUTO_INCREMENT,
`name` varchar(40) COLLATE utf8mb4_bin not NULL default '',
table_size decimal(15,2) NOT NULL DEFAULT '0.00',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=0;

mysql> select * from tet order by create_time;
+----+------------+------------+---------------------+
| id | name | table_size | create_time |
+----+------------+------------+---------------------+
| 1 | order_item | 34.00 | 2019-03-01 13:00:04 |
| 3 | order_item | 60.00 | 2019-03-15 13:00:04 |
| 2 | order_item | 54.00 | 2019-03-30 13:00:04 |
+----+------------+------------+---------------------+

想转换为：

name 2019-03-01 13:00:04 2019-03-15 13:00:04 2019-03-30 13:00:04
order_item 34.00 60.00 54.00


请教下sql如何写？

[AHUA1001]

SELECT t.name,MAX(IF(r=1,t.table_size,NULL)),MAX(IF(r=2,t.table_size,NULL)),MAX(IF(r=3,t.table_size,NULL)) FROM (
SELECT t.*,@R:=@R+1 r FROM (
SELECT t.*,@R:=0 FROM tet t ORDER BY DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%s:%i')
) t) t GROUP BY t.name

[rushm]

引用 1 楼 AHUA1001 的回复:

SELECT t.name,MAX(IF(r=1,t.table_size,NULL)),MAX(IF(r=2,t.table_size,NULL)),MAX(IF(r=3,t.table_size,NULL)) FROM (
SELECT t.*,@R:=@R+1 r FROM (
SELECT t.*,@R:=0 FROM tet t ORDER BY DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%s:%i')
) t) t GROUP BY t.name

-------------------
多谢，插入三条其他数据后有问题
insert into tet(name,table_size,create_time) values('cust_list',10.00,'2019-03-15 13:00:04');
insert into tet(name,table_size,create_time) values('cust_list',5.00,'2019-03-01 13:00:04');
insert into tet(name,table_size,create_time) values('cust_list',30.00,'2019-03-30 13:00:04');

mysql> SELECT t.name,MAX(IF(r=1,t.table_size,NULL)),MAX(IF(r=2,t.table_size,NULL)),MAX(IF(r=3,t.table_size,NULL)) FROM ( SELECT t.*,@R:=@R+1 r FROM ( SELECT t.*,@R:=0 FROM tet t ORDER BY DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%s:%i') ) t) t GROUP BY t.name;
+------------+--------------------------------+--------------------------------+--------------------------------+
| name | MAX(IF(r=1,t.table_size,NULL)) | MAX(IF(r=2,t.table_size,NULL)) | MAX(IF(r=3,t.table_size,NULL)) |
+------------+--------------------------------+--------------------------------+--------------------------------+
| cust_list | NULL | 5.00 | NULL |
| order_item | 34.00 | NULL | 60.00 |
+------------+--------------------------------+--------------------------------+--------------------------------+

[AHUA1001]

既然这个不行，那就简单粗暴一下吧。
SELECT t.name,
(SELECT tt.table_size FROM tet tt WHERE tt.name = t.name ORDER BY tt.create_time LIMIT 1) table_size_1,
(SELECT tt.table_size FROM tet tt WHERE tt.name = t.name ORDER BY tt.create_time LIMIT 1,1) table_size_2,
(SELECT tt.table_size FROM tet tt WHERE tt.name = t.name ORDER BY tt.create_time LIMIT 2,1) table_size_3
FROM tet t GROUP BY t.name ;