A表
Rm_code,P_no
1001 A101
1001 B102
1002 A101
1002 B102B 表Rm_Code ,P_No
1001 B102
1002 A101
求 A表存在记录,而B表不存在的记录
结果为:
Rm_Code,P_No
1001 A101
1002 B102
Rm_code,P_no
1001 A101
1001 B102
1002 A101
1002 B102B 表Rm_Code ,P_No
1001 B102
1002 A101
求 A表存在记录,而B表不存在的记录
结果为:
Rm_Code,P_No
1001 A101
1002 B102
=a.P_No
)
except
select * from 表B
select 1 from b 1什么意思
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
where not exists
(select * from B where A.Rm_code = B.Rm_code and A.P_no = B.P_no);
在A中查找不满足如下情况的内容,什么情况呢?
A的两个字段的值分别等于B的两个字段的值。
Incorrect syntax near the keyword 'except'.
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 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