MySQL实现差集(Minus)和交集(Intersect)测试报告

时间:2021-09-24 08:31:28

 可以用SQL实现同样的功能,就是麻烦了点。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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实现差集(Minus)和交集(Intersect)测试报告

MySQL实现交集

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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实现差集(Minus)和交集(Intersect)测试报告

MySQL实现差集

 

?
1
2
3
4
5
6
7
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实现差集(Minus)和交集(Intersect)测试报告