mysql - Mysql中三表无重复减去一张表的并集

Question

表:2018

No  Email

1   Lilly@gmail.com

2   brens@gmail.com

3   susan@gmail.com

4   resh@gmail.com

表:2017

No   Email

1    chitta@gmail.com

2    resh@gmail.com

3    brens@gmail.com

4    minu@gmail.com

表:2016

No   Email

1     brens@gmail.com

2     chitta@gmail.com

3     lisa@gmail.com

4     monay@gmail.com

5     many@gmail.com

表:2019

No   Email

1    brens@gmail.com

2    chitta@gmail.com

3    rinu@gmail.com

4    emma@gmail.com

我需要执行表 2018,2017,2016 的并集，没有任何重复的电子邮件减去表 2019，结果应该类似于

结果

No  Email

1    Lilly@gmail.com

2    susan@gmail.com

3    resh@gmail.com

4    minu@gmail.com

5   lisa@gmail.com

6   monay@gmail.com

7   many@gmail.com

Mysql 中不支持减号运算。

select a.*from(select *from y2018 union select *from y2017 where not exists(select *from y2018 where y2018.email=y2017.email ) union select *from y2016 where not exists(select *from y2018 where y2018.email=y2016.email ))a LEFT OUTER JOIN y2019 b on a.email=b.email  where b.email is null ;

这给出了结果，但没有消除 (2017 union 2016) 中的重复项

请有人帮助我

Answer 1

I need to perform Union of tables 2018,2017,2016 without any duplicate emails minus table 2019

最容易模拟/模拟减号/期望的是使用NOT IN()

查询

SELECT 
   (@ROW_NUMBER := @ROW_NUMBER + 1) AS 'No' 
 , unique_email.Email
FROM (

  SELECT 
    DISTINCT 
      years_merged.Email  
  FROM (

    SELECT 
     Email
    FROM 
     y2019
    UNION ALL
    SELECT 
     Email
    FROM 
     y2018
    UNION ALL
    SELECT 
     Email
    FROM
     y2017   
    UNION ALL
    SELECT 
     Email
    FROM 
     y2016     

  ) AS years_merged
  WHERE 
   years_merged.Email NOT IN(SELECT y2019.Email FROM y2019 ) 
  ORDER BY
   years_merged.Email ASC
) AS unique_email
CROSS JOIN (SELECT @ROW_NUMBER := 0) AS init
ORDER BY 
  @ROW_NUMBER ASC

结果

| No  | Email           |
| --- | --------------- |
| 1   | Lilly@gmail.com |
| 2   | lisa@gmail.com  |
| 3   | many@gmail.com  |
| 4   | minu@gmail.com  |
| 5   | monay@gmail.com |
| 6   | resh@gmail.com  |
| 7   | susan@gmail.com |

是的，顺序不同，但这是你能做的最好的事情，因为 SQL 标准定义 SQL 表要无序排序。

参见demo

但是使用 ... LEFT JOIN ... ON ... WHERE .. IS NULL 进行模拟/模拟减法/期望可能比 NOT IN() 优化得更好

参见demo