20161114182557
需求：查询user_tag不为101的所有用户

user_list表

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `user_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `user_list` VALUES ('1', 'QQ');
INSERT INTO `user_list` VALUES ('2', 'weibo');
INSERT INTO `user_list` VALUES ('3', 'weixin');
INSERT INTO `user_list` VALUES ('4', 'facebook');
INSERT INTO `user_list` VALUES ('5', 'twitter');

user_tag表

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `user_tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `tag_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

ALTER TABLE `user_tag` ADD INDEX `user_id` (`user_id`);
INSERT INTO `user_tag` VALUES ('1', '1', '100');
INSERT INTO `user_tag` VALUES ('2', '1', '101');
INSERT INTO `user_tag` VALUES ('3', '1', '102');
INSERT INTO `user_tag` VALUES ('4', '3', '101');
INSERT INTO `user_tag` VALUES ('5', '5', '102');

解决方法

方法一

1
select a.* from user_list as a where a.id  not in( select b.user_id from user_tag as b where b.tag_id = 101)

方法二

1
2
3
4
5
SELECT a.id, a.name FROM user_list a
left join user_tag b
on a.id = b.user_id
group by a.id, a.name
having instr(group_concat(b.tag_id), '101') = 0 or group_concat(b.tag_id) is null

方法三

1
SELECT * FROM user_list a WHERE NOT EXISTS(SELECT 1 FROM user_tag b WHERE a.id=b.user_id AND b.tag_id='101')

使用explain分析，方法三的效率是最高的。

20161114190231