用户标签 mysql_mysql查询用户标签

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)