mysql - SQL 多表 JOINS、GROUP BY 和 HAVING

Question

我有一个结构类似于此的表:

CREATE TABLE `user`
 (`id` int, `name` varchar(7));
CREATE TABLE `email`
  (`id` int, `email_address` varchar(50), `verified_flag` tinyint(1),`user_id` int);
CREATE TABLE `social`
 (`id` int,`user_id` int);

INSERT INTO `user`
 (`id`, `name`)
VALUES
 (1,'alex'),
 (2,'jon'),
 (3,'arya'),
 (4,'sansa'),
 (5,'hodor')
;
INSERT INTO `email`
     (`id`,`email_address`,`verified_flag`,`user_id`)
VALUES
 (1,'alex@gmail.com','1',1),
 (2,'jon@gmail.com','0',1),
 (3,'arya@gmail.com','0',3),
 (4,'sansa@gmail.com','1',4),
 (5,'reek@gmail.com','0',3),
 (6,'hodor@gmail.com','0',5),
 (7,'tyrion@gmail.com','0',1)
;
INSERT INTO `social`
     (`id`,`user_id`)
VALUES
 (1,4),
 (2,4),
 (3,5),
 (4,4),
 (5,4)
;

我要获取的是所有邮件:

1. 未验证
2. 属于没有(即 0 个)经过验证的电子邮件的用户
3. 属于没有(即 0)社交记录的用户

通过以下查询，我可以应用第一个和第三个条件，但不能应用第二个:

SELECT *
FROM `email`
INNER JOIN `user` ON `user`.`id` = `email`.`user_id`
LEFT JOIN `social` ON `user`.`id` = `social`.`user_id`
WHERE `email`.`verified_flag` = 0
GROUP BY `email`.`user_id`,`email`.`email_address`
HAVING COUNT(`social`.`id`) = 0

我怎样才能达到这个结果？这是 sqlfiddle还有

Answer 1

有趣且棘手的一个。

我看到你那里发生了一些事情。但是，当您的表变大时，拥有和子查询会成为一个非常的坏主意。

请参阅下面的方法。不要忘记设置索引!

SELECT * from email
LEFT JOIN social on email.user_id = social.user_id

 -- tricky ... i'm going back to email table to pick verified emails PER user
LEFT JOIN email email2 on email2.user_id = email.user_id AND email2.verified_flag = 1
WHERE
     -- you got this one going already :)
    email.verified_flag = 0

     -- user does not have any social record
    AND social.id is null

     -- email2 comes in handy here ... we limit resultset to include only users that DOES NOT have a verified email
    AND email2.id is null
ORDER BY email.user_id asc;