mysql 的列转行想找个函数,但是没有找到
目前是通过有条件的笛卡尔积完成的。
DROP IF EXISTS TABLE pyb_tmp_160315_2
CREATE TABLE pyb_tmp_160315_2 (
id INT,
NAME VARCHAR(200)
) ;
INSERT INTO pyb_tmp_160315_2
VALUES(1,’a’),(2,’a,b’),(3,’a,b,c’),(4,’b’),(5,’d,e’)
;
SELECT *
FROM pyb_tmp_160315_2
WHERE 1=1
;
SET @max_num:=0;
SELECT
t0.id,
SUBSTRING_INDEX(SUBSTRING_INDEX( REPLACE(REPLACE(t0.name,'[‘,”),’]’,”) ,’,’,t1.row_name_num),’,’,-1) AS name2
FROM (
SELECT LENGTH(wu.name)-LENGTH( REPLACE(wu.name,’,’,”))+1 AS name_num,
wu.*
FROM pyb_tmp_160315_2 wu
WHERE 1=1
) t0
JOIN
( SELECT t0.max_num_1 AS row_name_num
FROM (
SELECT @max_num:[email protected]_num+1 max_num_1
FROM pyb_tmp_160315_2
) t0
WHERE 1=1
AND max_num_1 <= ( SELECT MAX(LENGTH(wui.name)-LENGTH( REPLACE(wui.name,’,’,”))+1) AS row_department_num FROM pyb_tmp_160315_2 wui )
) t1
ON t1.row_name_num<=t0.name_num
;
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/3900.html