【MySQL】字段拆分需求实现（group_concat的逆操作）

来源：互联网发布：学大数据能干金融吗编辑：程序博客网时间：2024/06/05 07:50

主要参考资料：http://blog.chinaunix.net/uid-411974-id-3990697.html
实现DEMO（Mysql）

create table testbzm(id int,note varchar(20));insert into testbzm values(1,'xiaobai,xiaohuan,xiaohei');insert into testbzm values(2,'111,222,333');select * from testbzm;+----+----------------------+| id | note                 |+----+----------------------+|  1 | xiaobai,xiaohuan,xia ||  2 | 111,222,333          |+----+----------------------+2 rows in set-- 实现SQLSELECT    id,    SUBSTRING_INDEX(        SUBSTRING_INDEX(note, ',', seq),        ',' ,- 1    ) own_byFROM    ( -- SEQ 生成1-100序列SELECT        D1.seq + D2.seq * 10 seq    FROM        (select 0 seq union allselect 1 seq union allselect 2 seq union allselect 3 seq union allselect 4 seq union allselect 5 seq union allselect 6 seq union allselect 7 seq union allselect 8 seq union allselect 9 seq ) D1    CROSS JOIN (select 0 seq union allselect 1 seq union allselect 2 seq union allselect 3 seq union allselect 4 seq union allselect 5 seq union allselect 6 seq union allselect 7 seq union allselect 8 seq union allselect 9 seq ) D2) sequenceCROSS JOIN testbzmWHERE    seq BETWEEN 1AND (    SELECT        1 + LENGTH(note) - LENGTH(REPLACE(note, ',', '')))ORDER BY id+----+----------+| id | own_by   |+----+----------+|  1 | xiaohuan ||  1 | xia      ||  1 | xiaobai  ||  2 | 111      ||  2 | 222      ||  2 | 333      |+----+----------+6 rows in set

1 0