有如下一个表,需要将tag列的每个标签都提取出来,一行对应一个标签
mysql> select * from user_tag;
+--------+-----------------+
| userid | tag |
+--------+-----------------+
| 1 | css,go |
| 2 | mysql,sql,html |
| 3 | css,spring,php |
| 4 | css,java,go,sql |
| 5 | java,c |
| 6 | c |
+--------+-----------------+
6 rows in set (0.00 sec)
行转列实现过程
1.首先我们算出每个用户的标签个数:
select userid,LENGTH(a.tag)-LENGTH(REPLACE(a.tag,',',''))+1 from user_tag;
mysql> select userid,tag,LENGTH(tag)-LENGTH(REPLACE(tag,',',''))+1 from user_tag;
+--------+-----------------+-------------------------------------------+
| userid | tag | LENGTH(tag)-LENGTH(REPLACE(tag,',',''))+1 |
+--------+-----------------+-------------------------------------------+