MySQL实现差集(Minus)和交集(Intersect)_MySQL
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;
推荐阅读
-
MySQL去除重叠时间求时间差和的实现
-
mysql 如何获取两个集合的交集/差集/并集
-
MySQL实现差集(Minus)和交集(Intersect)测试报告_MySQL
-
MySQL实现差集(Minus)和交集(Intersect)
-
MySQL实现差集(Minus)和交集(Intersect)_MySQL
-
MySQL中使用INNER JOIN来实现Intersect并集操作_MySQL
-
MySQL中使用INNER JOIN来实现Intersect并集操作_MySQL
-
mysql的交集与差集_MySQL
-
MySQL不支持INTERSECT和MINUS及其替代方法_MySQL
-
mysql的交集与差集_MySQL