MySQL没有实现Minus和Intersect功能,就像它也没有实现cube的功能一样。
可以用SQL实现同样的功能,就是麻烦了点。
MySQL实现交集
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, 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;