mysql行转列和列传行

来源：互联网发布：淘宝购物车怎么置顶编辑：程序博客网时间：2024/05/17 23:48

1，行转列，字符串拆分

 -- 1.1 准备 CREATE TABLE T1(dt DATETIME,dv VARCHAR(100))INSERT INTO T1 (dt,dv) VALUES('2017-01-01 00:00:00','1,2,3,4,5,6'),('2017-01-01 00:01:00','10,20,30,40,50'); -- 序列表 CREATE TABLE sequence (id INT); -- 序列表填充存储过程 DELIMITER $$CREATE PROCEDURE `P`()    BEGINDECLARE i INT DEFAULT 0;WHILE i<60 DOINSERT INTO sequence(id) VALUES(i);SET i =i+1;END WHILE;    END$$DELIMITER ; -- 调用存储过程  CALL P(); -- 1.2,查询SELECTDATE_ADD(dt,INTERVAL a.id-1 SECOND) dt,SUBSTRING_INDEX(SUBSTRING_INDEX(dv,',',a.id), ',', -1)AS dvFROMsequence aCROSS JOIN(SELECTdt,CONCAT(dv, ',')AS dv,LENGTH(dv)- LENGTH(REPLACE(dv, ',', ''))+ 1 AS sizeFROM`T1`  )b ON a.id <= b.sizeORDER BY dt-- 结果dt                   dv      -------------------  --------2017-01-01 00:00:00  1       2017-01-01 00:00:01  2       2017-01-01 00:00:02  3       2017-01-01 00:00:03  4       2017-01-01 00:00:04  5       2017-01-01 00:00:05  6       2017-01-01 00:01:00  10      2017-01-01 00:01:01  20      2017-01-01 00:01:02  30      2017-01-01 00:01:03  40      2017-01-01 00:01:04  50

2，列传行，字符串拼接

CREATE TABLE T2(id INT,NAME VARCHAR(10));INSERT INTO T2(id,NAME) VALUES(1,'a'),(1,'b'),(2,'aa'),(2,'cc');SELECT id,GROUP_CONCAT(NAME) rFROM T2GROUP BY id;-- 结果    id  r       ------  --------     1  a,b          2  aa,cc         -- 注意区别于SELECT id,GROUP_CONCAT(NAME,":") rFROM T2GROUP BY id;    id  r        ------  ---------     1  a:,b:         2  aa:,cc:

0 0