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

主要参考资料：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

-- 实现SQL
SELECT
    id,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(note, ',', seq),
        ',' ,- 1
    ) own_by
FROM
    ( -- SEQ 生成1-100序列
SELECT
        D1.seq + D2.seq * 10 seq
    FROM
        (select 0 seq union all
select 1 seq union all
select 2 seq union all
select 3 seq union all
select 4 seq union all
select 5 seq union all
select 6 seq union all
select 7 seq union all
select 8 seq union all
select 9 seq ) D1
    CROSS JOIN (select 0 seq union all
select 1 seq union all
select 2 seq union all
select 3 seq union all
select 4 seq union all
select 5 seq union all
select 6 seq union all
select 7 seq union all
select 8 seq union all
select 9 seq ) D2
) sequence
CROSS JOIN testbzm
WHERE
    seq BETWEEN 1
AND (
    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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72