1.每个标签用户数
mysql> select a.name,count(*) from (
-> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.tag,',',b.help_topic_id+1),',',-1) as name
-> from user_tag a left join mysql.help_topic b
-> on b.help_topic_id < (LENGTH(a.tag)-LENGTH(REPLACE(a.tag,',',''))+1)
-> ) a group by a.name;
+--------+----------+
| name | count(*) |
+--------+----------+
| c | 2 |
| css | 3 |
| go | 2 |
| html | 1 |
| java | 2 |
| mysql | 1 |
| php | 1 |
| spring | 1 |
| sql | 2 |
+--------+----------+
9 rows in set (0.01 sec)
2.标签最多的用户
mysql> select (LENGTH(tag)-LENGTH(REPLACE(tag,',',''))+1) as tag_num,userid from user_tag order by tag_num desc limit 1;
+---------+--------+
| tag_num | userid |
+---------+--------+
| 4 | 4 |
+---------+--------+
1 row in set (0.00 sec)
3.用户最多的标签
mysql> select a.name,count(*) from (
-> SELECT a.userid,SUBSTRING_INDEX(SUBSTRING_INDEX(a.tag,',',b.help_topic_id+1),',',-1) as name
-> from user_tag a left join mysql.help_topic b
-> on b.help_topic_id < (LENGTH(a.tag)-LENGTH(REPLACE(a.tag,',',''))+1)
-> ) a group by a.name order by count(*) desc limit 1;
+------+----------+
| name | count(*) |
+------+----------+
| css | 3 |
+------+----------+
1 row in set (0.01 sec)