MySQL實現差集(Minus)和交集(Intersect)

來自： http://www.linuxidc.com/Linux/2014-06/103551.htm

MySQL沒有實現Minus和Intersect功能，就像它也沒有實現cube的功能一樣。
 
可以用SQL實現同樣的功能,就是麻煩了點。
 
drop table t1;
 
drop table t2;

create table t1(id int primary key,nickname varchar(20),playNum varchar(20));

create table t2(id int primary key,nickname varchar(20),playNum varchar(20));

insert into t1 values(1,1,10);

insert into t1 values(2,2,20);

insert into t1 values(3,3,30);

insert into t2 values(1,1,10);

insert into t2 values(2,2,200);

insert into t2 values(3,33,300);

commit;

MySQL實現交集
 
SELECT id, nickname, playNum, COUNT(*)
 
FROM (SELECT id, nickname, playNum

    FROM t1

    UNION ALL

    SELECT id, nickname, playNum

    FROM t2

    ) a

GROUP BY id, nickname, playNum

HAVING COUNT(*) > 1

MySQL實現差集
 
SELECT t1.id, t1.nickname, t1.playNum
 
FROM t1 LEFT JOIN t2 ON t1.id = t2.id

WHERE t1.nickname != t2.nickname

    OR t1.playNum != t2.playNum;

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htm

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主從服務器 http://www.linuxidc.com/Linux/2014-05/101599.htm

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htm

Ubuntu 12.04下源代碼安裝MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htm

--------------------------------------分割線 --------------------------------------

本文永久更新鏈接地址：http://www.linuxidc.com/Linux/2014-06/103551.htm