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如何寫？
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
-------------------
多謝，插入三條其他資料后有問題
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 |
+------------+--------------------------------+--------------------------------+--------------------------------+
uj5u.com熱心網友回復：
既然這個不行，那就簡單粗暴一下吧。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 ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/68527.html
標籤：MySQL
上一篇：影視制品租賃管理系統。。。
下一篇：遍歷一張表的資訊去查多張表