sql查询两张表的并集union和unionall 使⽤ union all 和 union
1.新建两张表：student、teacher
//学⽣表
create table student(
id int primary key,
name varchar(40)
);
//⽼师表
create table teacher(
id int primary key,
name varchar(40)
)
;
2.分别给两张表加⼊⼀条记录
insert into student(id,name) values(1,'student1');
insert into teacher(id,name) values(2,'teacher1');
3.查询并集：
⽅式⼀（不去重复）：
select * from student
union all
select * from teacher;
⽅式⼆（去掉重复）：
sql中union多表合并select * from student
union
select * from teacher;
带where⼦句的：
select * from student
where id = 1
union all
select * from teacher;
加字段区分记录是数据哪个表的:
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b;
加order by⼦句：
select * from
(
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b
) t
order by belongf desc,id desc;
查并集的总记录数：
select count(1) as mycount from (
select a.*, '1' as belongf from student a
union all
select b.*, '0' as belongf from teacher b
) d;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。