sql求两表的并集、交集、非交集、差集、结果集排序
时间：2021-07-01 10:21:17
帮助过：3人阅读
table A(
id int IDENTITY(
1,
1)
Not null primary key,
name varchar(
20)
not null default(
‘‘),
)
INSERT INTO [A](
[name])
VALUES(
‘a‘)
INSERT INTO [A](
[name])
VALUES(
‘b‘)
INSERT INTO [A](
[name])
VALUES(
‘c‘)
INSERT INTO [A](
[name])
VALUES(
‘d‘)
INSERT INTO [A](
[name])
VALUES(
‘e‘)
INSERT INTO [A](
[name])
VALUES(
‘f‘)
INSERT INTO [A](
[name])
VALUES(
‘g‘)
create table B(
id int IDENTITY(
1,
1)
Not null primary key,
name varchar(
20)
not null default(
‘‘),
)
INSERT INTO [B](
[name])
VALUES(
‘a‘)
INSERT INTO [B](
[name])
VALUES(
‘b‘)
INSERT INTO [B](
[name])
VALUES(
‘c‘)
INSERT INTO [B](
[name])
VALUES(
‘d‘)
INSERT INTO [B](
[name])
VALUES(
‘h‘)
INSERT INTO [B](
[name])
VALUES(
‘i‘)
INSERT INTO [B](
[name])
VALUES(
‘j‘)
SELECT * from A union select * from B --查询A\B表的并集重复的项只显示一个
SELECT * from A union all select * from B ----查询A\B表的并集重复的也显示
SELECT * from A union all select * from B order by id asc ---查询A\B表的并集重复的也显示，并按照id升序
SELECT * from A INTERSECT select * from B ----查询两表的交集
SELECT * from A EXCEPT select * from B ----查询A表中不与B表重复的记录
--查询A\B两表中所有非交集的记录
(SELECT * from A EXCEPT select * from B) union (SELECT * from B EXCEPT select * from A)
sql求两表的并集、交集、非交集、差集、结果集排序
标签：from sql color nbsp val 一个 col pre name
