Mysql拆分多列为多行_MySQL列分割成多行的方法

面试题目：表中有两列数据，一列是userid，一列是用户标签tag，标签类别总共有10个，每个用户可以拥有多个不同的标签，不同的标签之间用逗号隔开。问：输出每个标签的用户数，谁拥有的标签最多，哪个标签拥有用户最多？示例表user_tags

分析：表的内容如上，关键在于对tag列进行分割，需要采用如下这个具有连续数字序列的辅助表进行联结。mysql数据库自带的表help_topic，其help_topic_id从0到657

联结过程：

首先计算每个用户的标签个数，使用length()和replace()方法获取标签个数((length(tag) - length(replace(tag,',',''))) + 1)。

接着，使用mysql自带的`help_topic`表中的`help_topic_id`序列按照tag的个数进行联结help_topic_id" />联结得到每个用户有多行记录，行数等于标签个数(联结条件help_topic_id<标签个数)

最后使用select子句根据help_topic_id来截取(substring_index)标签。由于直接写出问题解答的SQL比较复杂。这里我们先创建一个视图，通过这个视图，可以很方便的解答需求。

CREATE VIEW labels(userid,help_topic_id,label)

as

select a.userid AS userid,

b.help_topic_id AS help_topic_id,

substring_index(

substring_index(a.tag,',',(b.help_topic_id + 1)),

',',

-1) AS label

from (user_tags as a inner join mysql.help_topic b

on (

b.help_topic_id <

((length(a.tag) - length(replace(a.tag,',',''))) + 1))

)创建视图

创建这个视图的关键在于理解：使用length()和replace()方法得到了每一行记录的用户标签个数：使用空字符串替换逗号，则整个字符串长度减少了逗号个数；使用substring_index()方法结合help_topic_id截取标签：先使用help_topic_id得到左起若干个数的标签，然后使用-1得到右起第一个标签。

最后回答三个问题：

-- 哪个用户拥有的标签最多SELECT c.userid,c.label_num

FROM (SELECT userid,COUNT(userid) as label_num from labels GROUP BY userid) as c

WHERE c.label_num =

(

select max(d.label_num)

FROM (SELECT userid,COUNT(userid) as label_num from labels GROUP BY userid) as d

);标签最多的用户

-- 每个标签的用户数SELECT label,COUNT(label) as user_num

from labels

GROUP BY label;

-- 哪个标签拥有的用户最多SELECT a.label,a.user_num

FROM (select label,count(label) as user_num from labels GROUP BY label) as a

WHERE a.user_num=

(

SELECT MAX(b.user_num)

FROM (select label,count(label) as user_num from labels GROUP BY label) as b

);