MySQL實現差集(Minus)和交集(Intersect)測試報告_Mysql

 可以用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, playNumFROM t1UNION ALLSELECT id, nickname, playNumFROM t2) aGROUP BY id, nickname, playNumHAVING COUNT(*) > 1

MySQL實現差集

 

SELECT t1.id, t1.nickname, t1.playNum FROM t1 LEFT JOIN t2 ON t1.id = t2.idWHERE t1.nickname != t2.nicknameOR t1.playNum != t2.playNum;