select * from clerk where clerk not in (select clerk from maininfo)
select clerk.branch,clerk.clerk,clerk.name from clerk where not exist(select * from maininfo where clerk.branch=maininfo.branch and clerk.clerk=maininfo.clerk)
create table clerk(branch varchar(10),clerk varchar(10),name varchar(10)) Insert into clerk select 'N01','101','張三' union all select 'N01','102','李四' union all select 'N01','103','王五' union all select 'N02','101','趙六' union all select 'N02','102','陳七'create table maininfo(id int, branch varchar(10), clerk varchar(10), money numeric(12,2)) Insert into maininfo select '1','N01','101','100.00' union all select '2','N01','101','99.85' union all select '3','N02','101','1524.00' union all select '4','N02','102','1125.00'select a.* from clerk a where not exists(select * from maininfo where branch=a.branch and clerk =a.clerk)--結果 branch clerk name -------------------- N01 102 李四 N01 103 王五
select a.* from clerk a where not exists(select * from maininfo where branch=a.branckh and clerk =a.clerk)
select * from tab1 a where not exists(select * from tab2 b where a.branch=b.branch and a.clerk=b.clerk)
create table tab1(branch char(4),clerk char(4),name char(8)) insert into tab1 select 'N01', '101', '张三' union all select 'N01', '102', '李四' union all select 'N01', '103', '王五' union all select 'N02', '101', '赵六' union all select 'N02', '102', '陈七'create table tab2(id int,branch char(4),clerk char(4),[money] money) insert into tab2 select 1, 'N01', ' 101', 100.00 union all select 2, 'N01', '101', 99.85 union all select 3, 'N02', '101', 1524.00 union all select 4, 'N02', '102', 1125.00 select * from tab1 a where not exists(select * from tab2 b where a.branch=b.branch and a.clerk=b.clerk) branch clerk name ------ ----- -------- N01 102 李四 N01 103 王五
select * from clerk where clerk not in (select clerk from maininfo)
where not exist(select * from maininfo where clerk.branch=maininfo.branch
and clerk.clerk=maininfo.clerk)
create table clerk(branch varchar(10),clerk varchar(10),name varchar(10))
Insert into clerk
select 'N01','101','張三'
union all select 'N01','102','李四'
union all select 'N01','103','王五'
union all select 'N02','101','趙六'
union all select 'N02','102','陳七'create table maininfo(id int, branch varchar(10), clerk varchar(10), money numeric(12,2))
Insert into maininfo
select '1','N01','101','100.00'
union all select '2','N01','101','99.85'
union all select '3','N02','101','1524.00'
union all select '4','N02','102','1125.00'select a.* from clerk a
where not exists(select * from maininfo where branch=a.branch and clerk =a.clerk)--結果
branch clerk name
--------------------
N01 102 李四
N01 103 王五
where not exists(select * from maininfo where branch=a.branckh and clerk =a.clerk)
insert into tab1 select
'N01', '101', '张三' union all select
'N01', '102', '李四' union all select
'N01', '103', '王五' union all select
'N02', '101', '赵六' union all select
'N02', '102', '陈七'create table tab2(id int,branch char(4),clerk char(4),[money] money)
insert into tab2 select
1, 'N01', ' 101', 100.00 union all select
2, 'N01', '101', 99.85 union all select
3, 'N02', '101', 1524.00 union all select
4, 'N02', '102', 1125.00 select * from tab1 a where not exists(select * from tab2 b where a.branch=b.branch and a.clerk=b.clerk) branch clerk name
------ ----- --------
N01 102 李四
N01 103 王五
freeheart1977(逍遥的心)、 crazyzcl(√小飞) 二位的不行的,因为我的表里面,不同的单位是存在相同的业务员工号的,呵呵,直接用not in无法排除。