mysql 列转行

pk	value
1	ET,AT
2	AT,BT
3	AT,DT
4	DT,CT,AT

 

value

AT

BT

CT

DT

ET

 SELECT DISTINCT
        t.id,
        t.role_id
        FROM
        (
        SELECT
        a.id,
        t.role_id
        SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id
        FROM
        blade_user a
        LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

思路：

1. substring_index函数对字符串进行截取
2. 通过substring_index函数特性，我们就需要知道字符串有多少个逗号，并且要告诉每个逗号的位置
3. 逗号个数=LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) 
4. 逗号位置=b.id< 逗号个数[+1]
5. 最后通过distinct函数将截取后的单个值进行去重

 

说明：help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 )

不明白为什么查询条件要小于这个字段的 字符串个数？

举例：

表blade_user：

id       role_id

1          ET,AT

2          AT,BT

表help_index

id

1

2

 

SELECT DISTINCT
        t.id,
        t.role_id
        FROM
        (
        SELECT
        a.id,
        t.role_id
        FROM
        blade_user a
        LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

查询结果 

pk       value             id

1          ET,AT            1

1          ET,AT            2

2          AT,BT             1

2          AT,BT             2

 查询顺序是先关联，然后select 

1、所以把符合条件的两个表查询出来，

每次执行select    SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id

这里查询会先取b.id的值，所以就自然筛选出   a.role_id 的  第1个，第2个。

 

查询结果

 SELECT DISTINCT
        t.id,
        t.role_id
        FROM
        (
        SELECT
        a.id,
        t.role_id
        SUBSTRING_INDEX( SUBSTRING_INDEX( a.role_id, ',', b.id + 1 ), ',', - 1 ) AS role_id
        FROM
        blade_user a
        LEFT JOIN help_index b ON b.id < ( LENGTH( a.role_id ) - LENGTH( REPLACE ( a.role_id, ',', '' ) ) + 1 ) ) t

 

 

pk       value             id

1          ET            1

1          AT            2

2          AT             1

2          BT             2