欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql列转行(列值不确定

程序员文章站 2022-03-11 18:41:11
...
有这样的一需求:
b列的值是不确定的,tag1—-tag50之间,但符合条件的tag最多为5个(不为空只有5个),转换成行。


[email protected] 08:17:54>select * from test;
+------------+-------+
| a | b |
+------------+-------+
| 2011-08-22 | tag1 |
| 2011-08-22 | tag2 |
| 2011-08-22 | tag3 |
| 2011-08-22 | tag30 |
| 2011-08-22 | tag40 |
| 2011-08-22 | NULL |
+------------+-------+


结果如下:


+------------+-------+-------+-------+-------+-------+
| a | b1 | b2 | b3 | b4 | b5 |
+------------+-------+-------+-------+-------+-------+
| 2011-08-22 | tag1 | tag2 |tag3 |tag30 | tag40 |
+------------+-------+-------+-------+-------+-------+


我的思路:

1、把行的转换成列:


+------------+-------+-------+-------+----|
| a | b1 |
+------------+-------+-------+-------+----|
| 2011-08-22 | tag1,tag2,tag3,tag30,tag40 |
+------------+-------+-------+-------+----|


2、再b1字段拆开来,拆成多个字段

解决方法:


[email protected] 08:24:46>select a,group_concat(b) from test where b is not null;
+------------+----------------------------+
| a | group_concat(b) |
+------------+----------------------------+
| 2011-08-22 | tag1,tag2,tag3,tag30,tag40 |
+------------+----------------------------+


分割函数([url]http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/[/url]):


[email protected] 08:25:58>CREATE FUNCTION SPLIT_STR(
-> x VARCHAR(255),
-> delim VARCHAR(12),
-> pos INT
-> )
-> RETURNS VARCHAR(255)
-> RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
-> LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
-> delim, '');
Query OK, 0 rows affected (0.00 sec)

[email protected] 08:28:11>select a,
-> split_str(group_concat(b), ',', 1) b1,
-> split_str(group_concat(b), ',', 2) b2,
-> split_str(group_concat(b), ',', 3) b3,
-> split_str(group_concat(b), ',', 4) b4,
-> split_str(group_concat(b), ',', 5) b5
-> from test
-> where b is not null;
+------------+------+------+------+-------+-------+
| a | b1 | b2 | b3 | b4 | b5 |
+------------+------+------+------+-------+-------+
| 2011-08-22 | tag1 | tag2 | tag3 | tag30 | tag40 |
+------------+------+------+------+-------+-------+