mysql查询用户标签

Question

有以下表结构

userid	tag
1	css,go
2	mysql,sql,html
3	css,spring,php
4	css,java,go,sql
5	java,c
6	c

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_tag
-- ----------------------------
DROP TABLE IF EXISTS `user_tag`;
CREATE TABLE `user_tag`  (
  `userid` int(11) NOT NULL,
  `tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`userid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_tag
-- ----------------------------
INSERT INTO `user_tag` VALUES (1, 'css,go');
INSERT INTO `user_tag` VALUES (2, 'mysql,sql,html');
INSERT INTO `user_tag` VALUES (3, 'css,spring,php');
INSERT INTO `user_tag` VALUES (4, 'css,java,go,sql');
INSERT INTO `user_tag` VALUES (5, 'java,c');
INSERT INTO `user_tag` VALUES (6, 'c');

SET FOREIGN_KEY_CHECKS = 1;

面试遇到的题

求

1：每个标签的用户数？

2：标签最多的用户？

3：用户最多的标签？

尝试用模糊查询或者find_in_set 都不太好处理 一时没有好的思路

Answer 1

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)