Mysql 有用户表、标签表，如何查询同时拥有某几个标签的用户。

Question

用户表 user id name 1 张三 2 李四 3 王五 标签表 tags id uid title 1 1 聪明 2 1 勤奋 3 2 聪明 4 2 勤奋 5 3 聪明 问题是如何查询同时包含某几个标签的人 比如同时包含 聪明，勤奋的人 SELECT `user`.* FROM `user` JOIN `tags` ON `user`.`id`=`tags`.`uid` WHERE `tags`.`title` = '聪明' AND `tags`.`title` = '勤奋' 这个语句查不出来结果，应该怎么写？ -- 表的结构 CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; INSERT INTO `user` (`id`, `name`) VALUES (1, '张三'), (2, '李四'), (3, '王五'); CREATE TABLE `tags` ( `id` int(9) NOT NULL, `uid` int(9) NOT NULL, `title` varchar(20) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; INSERT INTO `tags` (`id`, `uid`, `title`) VALUES (1, 1, '勤奋'), (2, 1, '聪明'), (3, 2, '肥宅'), (4, 2, '勤奋'), (5, 2, '聪明'), (6, 3, '聪明');

Answer 1

你那个查询的条件明显就不对

// tags表 `tags`.`title` = '聪明' AND `tags`.`title` = '勤奋' 这种数据根本就不存在

SELECT * FROM user
WHERE id IN (
    SELECT uid FROM tags WHERE title IN ('聪明', '勤奋') GROUP BY uid HAVING count(uid) = 2
);

Answer 2

SELECT u.* FROM user u WHERE ( SELECT count(id) FROM tags WHERE uid = u.id AND ( title = '聪明' OR title = '勤奋' ) ) > 1

Answer 3

select user.*,count(tags.uid)as num  from user left join tags on `user`.id = tags.uid 
where (`tags`.`title` = '聪明' OR `tags`.`title` = '勤奋') group by tags.uid 
having count(tags.uid) > 1