逗号分隔字段,序列化表方法实现列转行

逗号分隔字段,序列化表方法实现列转行

 

qujing表:

 

 

创建序列表:

create table tb_sequence(id int auto_increment not null,primary key(id));
insert into tb_sequence values(),(),(),(),(),(),(),(),();

 

 分解步骤1:

    SELECT
        user_name,
        concat(mobile, ',') AS mobile,
        length(mobile) - length(REPLACE(mobile, ',', '')) + 1 AS size
    FROM
        qujing AS b

 

 

 

分解步骤2:

SELECT * FROM
    tb_sequence AS a
CROSS JOIN (
    SELECT
        user_name,
        concat(mobile, ',') AS mobile,
        length(mobile) - length(REPLACE(mobile, ',', '')) + 1 AS size
    FROM
        qujing AS b
) AS b ON a.id <= b.size

 

 

列转行sql:

SELECT
    user_name,
    REPLACE (
        substring(
            substring_index(mobile, ',' , a.id),
            char_length(
                substring_index(mobile, ',', a.id - 1)
            ) + 1
        ),
        ',',
        ''
    ) AS mobile
FROM
    tb_sequence AS a
CROSS JOIN (
    SELECT
        user_name,
        concat(mobile, ',') AS mobile,
        length(mobile) - length(REPLACE(mobile, ',', '')) + 1 AS size
    FROM
        qujing AS b
) AS b ON a.id <= b.size