相关文章
- sql 的 join、left join、full join的区别图解总结，测试，注意事项
1.结论示意图
- 对于
intersect、minus，oracle支持，mysql不支持，可以变通（in或exists）实现
2.创建表和数据
-- 建表
drop table if exists student; -- oralce 不支持 if exists
create table student (
id int
);
-- 造数据4条
insert into student (id) values (1);
insert into student (id) values (2);
insert into student (id) values (3);
insert into student (id) values (4);
-- 查看表数据
select * from student;
3.查询
3.1. A集合
-- A集合
select * from student where id in (1,2,3);
3.2. B集合
-- B集合
select * from student where id in (2,3,4);
3.3. 交集intersect(A ∩ B)
- oracle支持，mysql不支持（可以变通实现）
-- intersect(A ∩ B)。交集
select * from student where id in (1,2,3)
intersect
select * from student where id in (2,3,4);
-- 变通实现
select * from student where id in (1,2,3)
and id in (
select id from student where id in (2,3,4));
3.4. 差集minus
- oracle支持，mysql不支持（可以变通实现）
3.4.1.左差集minus(A – B)
-- minus(A - A ∩ B)。左差集
select * from student where id in (1,2,3)
minus
select * from student where id in (2,3,4);
-- 变通实现
select * from student where id in (1,2,3)
and id not in (
select id from student where id in (2,3,4));
3.4.2 右差集minus(服务器托管网B – A)
-- minus(A - A ∩ B)。右差集
select * from student where id in (2,3,4)
minus
select * from student where id in (1,2,3);
-- 变通实现
select * from student where id in (2,3,4)
and id not in (
select id from student where id in (1,2,3));
3.5. 并集union(A ∪ B)
-- union(A ∪ B)。并集（去重）
select * from student where id in (1,2,3)
union
select * from student where id in (2,3,4);
3.6. 和集 union all(A + B)
-- union all(A + B)。和集（不去重）
select * from student where id in (1,2,3)
union all
select * from student where id in (2,3,4);
3.7. 补集(A minus B) union (B minus A)[(A – B) ∪ (B – A)]或 (A union B) minus (A intersect B)[(A ∪ B) – (A ∩ B)] 。A ∩ B在A ∪ B的补集。
- oracle支持，mysql不支持（可以变通实现）
-- 算法1：`(A minus B) union (B minus A)`[(A - B) ∪ (B - A)]。A ∩ B在A ∪ B的补集。
(
select * from student where id in (1,2,3)
minus
select * from student where id in (2,3,4)
)
union
(
select * from student where id in (2,3,4)
minus
select * from student where id in (1,2,3)
);
-- 算法1：变通实现
(
select * from student where id in (1,2,3)
and id not in (
select id from student where id in (2,3,4))
)
union
(
select * from student where id in (2,3,4)
and id not in (
select id from student where id in (1,2,3))
);
-- 算法2：`(A union B) mi服务器托管网nus (A intersect B)`[(A ∪ B) - (A ∩ B)]
-- `(union) minus (intersect)`[(A ∪ B) - (A ∩ B)]。A ∩ B在A ∪ B的补集。
(
select * from student where id in (2,3,4)
union
select * from student where id in (1,2,3)
)
minus
(
select * from student where id in (2,3,4)
intersect
select * from student where id in (1,2,3)
);
-- 算法2：变通实现
select * from
(
select * from student where id in (1,2,3)
union
select * from student where id in (2,3,4)
)
where id not in
(
select id from student where id in (1,2,3)
and id in (
select id from student where id in (2,3,4))
);
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: QUIC 协议：特性、应用场景及其对物联网/车联网的影响
什么是 QUIC 协议 QUIC（Quick UDP Internet Connections）是由谷歌公司开发的一种基于用户数据报协议（UDP）的传输层协议，旨在提高网络连接的速度和可靠性，以取代当前互联网基础设施中广泛使用的传输控制协议（TCP）。 QUI…
