mysql - SQL多表JOINS，GROUP BY和HAVING

我有一个表的结构有点类似：

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,'[email protected]','1',1),
 (2,'[email protected]','0',1),
 (3,'[email protected]','0',3),
 (4,'[email protected]','1',4),
 (5,'[email protected]','0',3),
 (6,'[email protected]','0',5),
 (7,'[email protected]','0',1)
;
INSERT INTO `social`
     (`id`,`user_id`)
VALUES
 (1,4),
 (2,4),
 (3,5),
 (4,4),
 (5,4)
;

我想要的是所有的电子邮件：
未经核实的
属于一个没有，即0，验证邮件的用户
属于没有，即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的

最佳答案

您可以使用以下查询：

SELECT e.`id`, e.`email_address`, e.`verified_flag`, e.`user_id`
FROM (
   SELECT `id`,`email_address`,`verified_flag`,`user_id`
   FROM `email`
   WHERE `verified_flag` = 0) AS e
INNER JOIN (
   SELECT `id`, `name`
   FROM  `user` AS t1
   WHERE NOT EXISTS (SELECT 1
                     FROM  `email` AS t2
                     WHERE `verified_flag` = 1 AND t1.`id` = t2.`user_id`)

         AND

         NOT EXISTS (SELECT 1
                     FROM  `social` AS t3
                     WHERE t1.`id` = t3.`user_id`)
) AS u ON u.`id` = e.`user_id`;

此查询使用两个派生表：
e实现第一个条件，即返回所有未经验证的电子邮件
u实现第二和第三个条件，即它返回一组所有没有经过验证的电子邮件和社会记录的用户。
在INNER JOIN和e之间执行u将返回所有满足条件1的电子邮件，这些电子邮件属于满足条件2和3的用户。
Demo here
您也可以使用此查询：

SELECT *
FROM `email`
WHERE `user_id` IN (
   SELECT `email`.`user_id`
   FROM `email`
   INNER JOIN `user` ON `user`.`id` = `email`.`user_id`
   LEFT JOIN `social` ON `user`.`id` = `social`.`user_id`
   GROUP BY `email`.`user_id`
   HAVING COUNT(`social`.`id`) = 0 AND
          COUNT(CASE WHEN `email`.`verified_flag` = 1 THEN 1 END) = 0 )

子查询用于选择所有满足条件2和3的user_id。条件1是多余的，因为如果用户没有验证过的电子邮件，那么验证过的电子邮件就不可能与该用户相关。
Demo here

关于mysql - SQL多表JOINS，GROUP BY和HAVING，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33334566/