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