面试题目:表中有两列数据,一列是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
);