MySql怎么实现交集和差集集合操作?

由于目前MySql中还没有实现集合的交集和差集操作，所以在MySql中只能通过其他的方式实现。

假设有这样一个需求，公司统计员工连续两个月的满勤情况，如果连续两个月满勤，则发放满勤奖；如果只有一个月满勤，则发送鼓励邮件。这个需求在数据库的部分该如何实现？

以下分别为员工3月份满勤和4月份满勤的示例表：

--3月份满勤的员工
create table employee_202103(
employee_id decimal(18) primary key comment '员工工号',
name varchar(12),
gender char(1),
age int comment '年龄'
);
insert into employee_202103 values(1,"李一","1",23);
insert into employee_202103 values(2,"李二","1",24);
insert into employee_202103 values(3,"李三","2",23);
insert into employee_202103 values(4,"李四","1",26);
insert into employee_202103 values(5,"李五","1",24);

--4月份满勤的员工
create table employee_202104(
employee_id decimal(18) primary key comment '员工工号',
name varchar(12),
gender char(1),
age varchar(3) comment '年龄'
);
insert into employee_202104 values(1,"李一","1",23);
insert into employee_202104 values(2,"李二","1",24);
insert into employee_202104 values(3,"李三","2",23);
insert into employee_202104 values(6,"李六","1",24);
insert into employee_202104 values(7,"李七","2",23);

 一、并集

并集操作符union，所得结果为去除了重复的元素(连接的两个集合中都存在)的结果集。union all则保留重复的元素，所以union all的结果集行数等于连接的各集合的行数之和。

根据以上union的定义，union可以查询出至少有一个月满勤的员工。

select * from employee_202103 t1 
UNION
select * from employee_202104 t2;

 

二、交集 

SQL规范中交集的操作符为intersect，结果为两个连接的集合中都存在的元素集合。

交集可以查询出连续两个月都满勤的员工，目前MySql中没有实现，可以通过以下方式实现。

--exists方式
select * from employee_202103 t1 where  EXISTS (
	select * from employee_202104 t2 where t1.employee_id= t2.employee_id
);

--in方式(效率没有exists高)
select * from employee_202103 t1 where t1.employee_id in (
	select employee_id from employee_202104 t2
);

--取两个集合并集并且重复的那部分
select employee_id,name,gender,age from (
	select * from employee_202103 t1
union all
	select * from employee_202104 t2
) t1 GROUP BY employee_id,name,gender,age HAVING COUNT(*)=2;

结果如下：

 

三、差集

SQL规范中交集的操作符为except，结果为两个连接的集合中仅第一个集合中存在的元素集合。

交集可以查询出两个月中仅一个月满勤的员工，给予不同的鼓励。目前MySql中没有实现，可以通过以下方式实现。以下为上月满勤本月没有满勤的员工：

--not exists实现
select * from employee_202103 t1 where not EXISTS (
select * from employee_202104 t2 where t1.employee_id = t2.employee_id 
);
 
--not in实现(效率没not exists高) 
select * from employee_202103 t1 where employee_id not in ( select employee_id from employee_202104 t2);
 
--LEFT JOIN方式 
select t1.* from employee_202103 t1 LEFT JOIN employee_202104 t2 on t1.employee_id = t2.employee_id 
 where t2.employee_id is null;

 结果如下：

同样可以把第二个月的表放在前面查询出上月没有满勤本月满勤的员工。

集合操作有一些限制，如下：

1.查询的两个数据集合必须有同样数目的列数。

2.两个数据集对应列的数据类型要一样。

所以如果是两个不同类型的表需要根据某个条件取第一个表中存在而第二个表中没有相关联的数据，建议使用not exists。需要根据某个条件取第一个表中存在而第二个表中有相关联的数据，建议使用exists。