34,575
社区成员
发帖
与我相关
我的任务
分享
select a.* from a left join b
on a.rm_code=b.rm_code and a.P_no=b.P_no
where b.Rm_code is null
create table A
(
Rm_code int not null,
P_no nvarchar(10) not null
)
insert into A(Rm_code,P_no) values(1001,'A101')
insert into A(Rm_code,P_no) values(1001,'B102')
insert into A(Rm_code,P_no) values(1002,'A101')
insert into A(Rm_code,P_no) values(1002,'B102')
create table B
(
Rm_code int not null,
P_no nvarchar(10) not null
)
insert into B(Rm_code,P_no) values (1001,'B102')
insert into B(Rm_code,P_no) values(1002,'A101')
select * from A except select * from B
select * from A
where not exists
(select * from B where A.Rm_code = B.Rm_code and A.P_no = B.P_no);
use tempdb;
/*
create table A
(
Rm_code int not null,
P_no nvarchar(10) not null
);
insert into A(Rm_code,P_no)
values
(1001,'A101'),
(1001,'B102'),
(1002,'A101'),
(1002,'B102');
create table B
(
Rm_code int not null,
P_no nvarchar(10) not null
);
insert into B(Rm_code,P_no)
values
(1001,'B102'),
(1002,'A101');
*/
--求 A表存在记录,而B表不存在的记录
select * from A
except
select * from B;
select * from 表A
except
select * from 表B
select * from a where not exists(select 1 from b where Rm_Code=a.Rm_Code and P_No
=a.P_No
)