(go) MySQL group_concat reverse application implementation (MySQL row)

I used MySQL to know that she had a good function of group_concat function, which is very convenient.

Click (here) to collapse or open

1. SELECT
2. *
3. From
4. Group_test;
6. SELECT
7. Id
8. Group_concat (sub_id)
9. From
10. ' Group_test '
11. GROUP by
12. Id

Now the demand is that there is a similar result set in Figure two above, the need to convert the column two split rows record


We know that if it's a single record, it's easy to implement Substring_index

Click (here) to collapse or open

1. Select Id,substring_index (sub_id, ', ', 1) from Group_test where id=3
2. UNION
3. Select Id,substring_index (Substring_index (sub_id, ', ', 2), ', ', -1) from Group_test where id=3
4. UNION
5. Select Id,substring_index (Substring_index (sub_id, ', ', 3), ', ', -1) from Group_test where id=3

But what if it's n? You can also use Substring_index to do this, except that you need a configuration table that is implemented via cross join, first look at the Intersect join

Click (here) to collapse or open

1. SELECT
2. *
3. From
4. (Select 1 UNION select 2) t1
5. Cross JOIN (select 3 UNION select 4) t2

The following is the implementation of our requirements through cross joins and Substring_index, first building a configuration table

Click (here) to collapse or open

1. create table digits  (Digit int (1));
2. insert into digits
3. VALUES
4.      (0),
5.      (1),
6.      (2),
7.      (3),
8.      (4),
9.      (5),
10.      (6),
11.      (7),
12.      (8),
13.      (9);
14. create table sequence  (Seq int (3));
15. insert into sequence  (
16.     select
17.          d1.digit + d2.digit * 10
18.      From
19.         digits D1
20.     cross JOIN digits D2
21. );

And then

Click (here) to collapse or open

1. SELECT
2. Id
3. Substring_index (
4. Substring_index (sub_id, ', ', seq),
5. ', ',-1
6. ) sub_id,
7. Seq
8. From
9. Sequence
10. Cross JOIN Group_test
11. WHERE
12. Seq between 1
13. and (
14. SELECT
15. 1 + Length (sub_id)-Length (REPLACE (sub_id, ', ', '))
16. )
17. ORDER by
18. Id
19. sub_id;

Original sticker: http://blog.chinaunix.net/uid-411974-id-3990697.html

(go) MySQL group_concat reverse application implementation (MySQL row)