mysql行转列（日期）

2019-03-21 03:07:11 499 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Mysql动态行转列（不使用存储过程） Mysql动态行转列（不使用存储过程）数据：行转列后的数据： SQL如下（示例）：创建表格 CREATE TABLE `dome` ( `id` INT NOT NULL AUTO_INCREMENT, `testdate` DATE DEFAULT NULL COMMENT '时间', `testtext` VARCHAR(50) DEFAULT NULL COMMENT '内容', `stature` DOUBLE DEFAULT NULL COMMENT '身高', PR 阅读详情

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如何写？

...全文

AUTOSAR FEE 换页机制深度解析：从原理到实现逻辑没有磨损均衡的EEPROM模拟，如同在沙地上建造城堡 ——AUTOSAR存储系统设计箴言阅读详情

rushm

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

AHUA1001 2019-03-22

打赏
举报

既然这个不行，那就简单粗暴一下吧。
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 ;

rushm 2019-03-21

打赏
举报

引用 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 2019-03-21

打赏
举报

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

mysql行转列（日期）

相关推荐