MySQL列分割成多行的方法

南桥少年

ZGNB！QYNB！

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

分析：表的内容如上，关键在于对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`不一样。如下。

联结得到每个用户有多行记录，行数等于标签个数（联结条件help_topic_id&amp;amp;amp;amp;lt;标签个数）

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

CREATE VIEW labels(userid,help_topic_id,label)
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