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

壹頁書發表於2014-06-20
MySql
MySQL沒有實現Minus和Intersect功能，就像它也沒有實現cube的功能一樣。

可以用SQL實現同樣的功能,就是麻煩了點。
  1. drop table t1;
  2. drop table t2;
  3. create table t1(id int primary key,nickname varchar(20),playNum varchar(20));
  4. create table t2(id int primary key,nickname varchar(20),playNum varchar(20));
  5. insert into t1 values(1,1,10);
  6. insert into t1 values(2,2,20);
  7. insert into t1 values(3,3,30);
  8. insert into t2 values(1,1,10);
  9. insert into t2 values(2,2,200);
  10. insert into t2 values(3,33,300);
  11. commit;


MySQL實現交集
  1. SELECT id, nickname, playNum, COUNT(*)
  2. FROM (SELECT id, nickname, playNum
  3.     FROM t1
  4.     UNION ALL
  5.     SELECT id, nickname, playNum
  6.     FROM t2
  7.     ) a
  8. GROUP BY id, nickname, playNum
  9. HAVING COUNT(*) > 1


MySQL實現差集
  1. SELECT t1.id, t1.nickname, t1.playNum
  2. FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  3. WHERE t1.nickname != t2.nickname
  4.     OR t1.playNum != t2.playNum;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1190027/，如需轉載，請註明出處，否則將追究法律責任。

相關文章