MySQL如何实现字段的split功能——名言SQL练习求最热门的10个标签

建表语句见文章：
SQL练习1——名言SQL练习

数据是这样的：

求最热门的10个标签是什么？
SQL实现是这样写的：

select tmp.tag,count(tag) amount from (
select q.id ,q.tags,ht.help_topic_id,
substring_index( substring_index(q.tags,'/',ht.help_topic_id+1) ,'/',-1) tag
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1
) tmp group by tag order by amount desc limit 10;

很多人问为我为什么要这样写？思路是什么？下面我就来说说为什么这样写
实现步骤：
首先查看所有数据的标签

select tags from quote;

可以看到数据是长这样的：

思路：每一条数据的tags字段里的数据内容是多个标签并且是以’/'来分隔的，既然tags标签是长这样的，那求最热门的10个标签就肯定得先对tags字段进行切分才能对再统计热门标签。这道题的难点就在于如何进行分割。

实现步骤：
1、查询所有数据的标签

select tags from quote;

2、测试substring_index函数作用

select tags,substring_index(tags,'/',1) from quote ;

3、查看mysql.help_topic，使用系统表mysql.help_topic的id列数据充当循环变量（因为id是连续不间断的适合做循环变量；这个自己建一张类似的表也可以）

select * from mysql.help_topic ht ;

4、连接quote表和系统表，以确定每条数据的tags字段数据需要被切分成几段

select q.id ,q.tags,ht.help_topic_id
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1;

这里可能有点不好理解，再说一下，你如果明白了的话就跳过吧：
length (q.tags) - length (replace(q.tags,’/’,’’))+1 :作用是通过分隔符确定当前数据需要被切分成几段，ht.help_topic_id值刚好可以充当每次截取（每个标签）的循环变量
看了这张图你就知道为啥要求这样一个值了，另外，因为length (q.tags) - length (replace(q.tags,’/’,’’))求得的是分隔符（‘/’）的个数，而截取后的标签个数是比分隔符多一个的，所以最后还要+1

5、通过substring_index函数和循环变量依次截取每个标签段

select q.id ,q.tags,ht.help_topic_id,
substring_index(q.tags,'/',ht.help_topic_id+1)  tag
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1;

6、通过对单次截取的标签段进行二次截取，获得每个单独的标签

select q.id,q.tags,ht.help_topic_id,substring_index(q.tags,'/',ht.help_topic_id+1),  
substring_index( substring_index(q.tags,'/',ht.help_topic_id+1) ,'/',-1) tag
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1;

7、最终的查询语句：

select tmp.tag,count(tag) amount from (
select q.id ,q.tags,ht.help_topic_id,
substring_index( substring_index(q.tags,'/',ht.help_topic_id+1) ,'/',-1) tag
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1
) tmp group by tag order by amount desc limit 10;