MySQL的INTERSECT替代方案

Question

我已经花了大部分时间尝试删除交集，因为 MySQL 并不支持。如果有人能提供一些指示，那将非常有帮助。

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
FROM user U, user_utilisation UU 
WHERE U.id_user = UU.id_user AND cp >= 1 AND cp <= 3000 AND sexe = 'M' AND UU.id_mailing = 6 
GROUP BY U.id_user 
HAVING age >= 1 AND age <= 100 
ORDER BY nom, prenom

INTERSECT 

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
FROM user U, user_utilisation UU 
WHERE U.id_user = UU.id_user AND cp >= 1 AND cp <= 3000 AND sexe = 'M' AND UU.id_mailing = 7 
GROUP BY U.id_user 
HAVING age >= 1 AND age <= 100 
ORDER BY nom, prenom

我尝试使用JOIN，但目前的情况如下：

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(naissance, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(naissance, '00-%m-%d')) AS age 
FROM user U, user_utilisation UU 
WHERE U.id_user = UU.id_user AND cp >= 1 AND cp <= 3000 AND sexe = 'M' 
    AND UU.id_user IN (select id_user from user_utilisation where id_mailing = 6 OR id_mailing = 7) 
HAVING age >= 1 AND age <= 100 
ORDER BY nom, prenom

但是如果移除GROUP BY，我发现查询选中了2条记录，其中id_mailing = 1，而GROUP BY则隐藏了错误的记录。我相信这可能会引起问题... user_utilisation只有三个字段：id_user、id_mailing和date。

- Narcil

---

很抱歉，我可能错了，但这样的交集怎么可能返回任何结果呢？第一个选择记录如果它有id_mailing = 6，而第二个选择记录如果它有id_mailing = 7。我认为不能同时存在。 - Aioros

您能提供一个查询实例，说明您正在尝试获取什么吗？ - ESG

Answer 1

根据您的查询，我认为：

1. 您想要获取参与邮件ID 6和7的所有用户（这就是为什么要使用INTERSECT，对吗？）。
2. 您想通过条件限制这些用户并执行年龄计算。

如果我对您的意图有正确的理解（完全不确定！），则您根本不需要使用INTERSECT，您只需使用所需的条件选择所需的用户，并通过JOIN限制其同时参与邮件6和7：

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
FROM user U
INNER JOIN user_utilisation UU on U.id_user = UU.id_user and UU.mailing_id = 6
INNER JOIN user_utilisation UU2 on U.id_user = UU2.id_user and UU2.mailing_id = 7
WHERE cp >= 1 AND cp <= 3000 AND sexe = 'M' 
HAVING age >= 1 AND age <= 100 
ORDER BY nom, prenom

- DWright

---

似乎正是我正在寻找的。谢谢！ - Narcil

很高兴能够帮到您！如果您认为这个回答解决了您的问题，您可以点击答案右上角的大勾号“接受”它作为答案。 - DWright

Answer 2

你可以尝试以下内容：

SELECT *
  FROM
      (SELECT *
            , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
        FROM user U
             INNER JOIN user_utilisation UU 
                     ON U.id_user = UU.id_user
        WHERE cp >= 1 AND cp <= 3000 
              AND sexe = 'M'
              AND UU.id_mailing = 6 
        GROUP BY U.id_user 
        HAVING age >= 1 AND age <= 100) QUERY1
 WHERE EXISTS(SELECT *
            , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age 
        FROM user U
             INNER JOIN user_utilisation UU 
                     ON U.id_user = UU.id_user
        WHERE cp >= 1 AND cp <= 3000 
              AND sexe = 'M'
              AND UU.id_mailing = 7
              AND U.id_user = QUERY1.id_user
        GROUP BY U.id_user 
        )
ORDER BY nom, prenom

上述查询的技巧在于 WHERE EXISTS( ... ) 子句充当过滤器，类似于您之前使用的 INTERSECT。其想法是您只想选择那些符合条件的记录，即 QUERY1 查询中至少有一个记录与 EXITS 子句中的查询相匹配，该查询基于 id_user 与 QUERY1 查询中的 id_user 相同。希望这有些意义。

如果您提供 User 表的结构，我可能可以为您微调一下。顺便说一下，使用那些全选 SELECT * 语句不是一个好主意。

- Mike Dinescu

Answer 3

我认为你可以简化查询。它有几个奇怪之处。首先，查询没有使用正确的连接语法。其次，查询仅使用having子句来过滤select中的别名。在这种情况下，我建议使用子查询。

看起来你想要获取两个非常相似的查询所定义的“组”中都存在的用户（只有where子句不同）。以下可能是你要找的：

select t.*
from (SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age,
             (case when cp >= 1 AND cp <= 3000 AND sexe = 'M' AND UU.id_mailing = 6 then 'FirstGroup'
                   when cp >= 1 AND cp <= 3000 AND sexe = 'M' AND UU.id_mailing = 7 then 'SecondGroup'
              end) as thegroup
      FROM user U join user_utilisation UU 
           on U.id_user = UU.id_user
     ) t
where thegroup is not null and age between 1 and 100
GROUP BY U.id_user
having max(thegroup) <> min(thegroup)
ORDER BY nom, prenom

having子句是一种简写方式，表示用户同时属于两个组。

- Gordon Linoff