mysql的合,交,并,差集

  今天项目中用到多张表的交集,为此废了一番功夫,特记录下来,方便和我遇到同样困惑的予以帮助!

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(差集)

---

差集也不多说,`NOT IN` 就可以解决,(效率低) 进行大表左联小表

select * from t1 left join t2 on t1.id = t2.id where t2.id is null

如果分不清大小表则

select * from t1 left join t2 on t1.id = t2.id where t2.id is null
union
select * from t2 left join t1 on t2.id = t1.id where t1.id 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集合!ok!