Mysql的合,交,并,差集

刺骨的言语ヽ痛彻心扉 2022-05-17 05:49 169阅读 0赞

# UNION \[ALL\] (并,合集) #

首先我们简单说下合集和并集，这两个没多大区别，对于A,B两个集合合起来的数据就叫合集，并集就是合起来的数据中重复的不要，分别对应关键字`UNION ALL`和`UNION`

eg:SELCT id FROM t1 UNION SELECT id FROM t2

说明几点:  
对于`union`的前部分查询和后部分查询不能有`GROUP BY`,`ORDER BY`等字段,只有是在整个的最后才能有`GROUP BY`,`ORDER BY`等字段!

eg:
    SELCT id FROM t1 
    UNION 
    SELECT id FROM t2 
    UNION 
    SELECT id FROM t3 
    GROUP BY id 
    ORDER BY id LIMIT 0,5

*  UNION的每个子查询必须有相同数量的列，每一个对应的列必须有相同类型的数据
 *  UNION的最终查询结果只按第一个子查询的列为准

# EXCEPT(差集) #

求两个查询结果的差集，Mysql直接提供了关键字`not in`，但是`not in`的效率低下，出现例如求一个上千查询结果在上万查询结果的差集，这时建议使用左链接的方式求查询。

使用`not in`：

select id from usertable
    where id not in (select id from blog)

使用左连接：

select id FROM usertable LEFT JOIN (select id as i from blog) as t1 ON usertable.id=t1.i where t1.i IS NULL

# INTERSECT(交集) #

交集就比较有意思了，Mysql不支持`INTERSECT`关键字，我们只能通过别的办法来解决了，首先我看了有篇博客的解决办法,一看就是不正确的，后做了实验发现如果只有2个结果集正确,如果有多个结果集就不正确了,我先把那种方法给出来,当然给出来只是作为见识，不要记这种方法就ok了,后面我会给出完美解决交集的方法，也不麻烦，没有网上说的那么麻烦！

首先给出表语句：

CREATE TABLE t1( id1 INTEGER, PRIMARY KEY (id1) );
    CREATE TABLE t2( id2 INTEGER, PRIMARY KEY (id2) );
    CREATE TABLE t3( id3 INTEGER, PRIMARY KEY (id3) );
    INSERT INTO t1(id1) VALUES(1);
    INSERT INTO t1(id1) VALUES(3);
    INSERT INTO t1(id1) VALUES(5);
    INSERT INTO t1(id1) VALUES(7);
    INSERT INTO t1(id1) VALUES(9);
    INSERT INTO t2(id2) VALUES(2);
    INSERT INTO t2(id2) VALUES(4);
    INSERT INTO t2(id2) VALUES(6);
    INSERT INTO t2(id2) VALUES(8);
    INSERT INTO t2(id2) VALUES(9);
    INSERT INTO t3(id3) VALUES(2);
    INSERT INTO t3(id3) VALUES(4);
    INSERT INTO t3(id3) VALUES(6);
    INSERT INTO t3(id3) VALUES(8);
    INSERT INTO t3(id3) VALUES(9);
    INSERT INTO t3(id3) VALUES(11);
    COMMIT;

我这里故意用id1,id2,id3目的是为了后面的说明!

下面给出错误写法:

SELECT id FROM ( SELECT id1 id FROM t1 UNION ALL SELECT id2 id FROM t2 ) a GROUP BY id HAVING count(id)>1

注意:这种写法,如果是2个结果集,最终结果结果正确,如果是多个结果集,明显不正确,因为他是求的合集,之后又用分组和组函数,虽然我不知道为什么结果是正确的,但是明显从根本就是错的,所以我就做了个实验,用3张表,一下子就错了!

下面给出正确解法:

SELECT t1.id1 FROM t1 INNER JOIN t2 ON t1.id1 = t2.id2 INNER JOIN t3 ON t2.id2 = t3.id3

是不是很简单?比第一种简单多了吧!并且其完全正确,我们分析下就知道,`INNER JOIN`是左右都符合条件才可以,因此,其表连接下来的不就是交集么?至于你查的时候无论你是查t1.id1,还是t2.id2,还是t3.id3其结果都是一样的,因为留下的都是符合条件的,其在A集合,又在B集合,又在C集合!