insert into C select ( select EMPID,LEAVEID from A eft join B on A.[TABLE] =B.[TABLE] where not exists (select 1 from A left join B on A.[TABLE] =B.[TABLE] left join C on B.LEAVEID=C.LEAVEID ))f
create table #a (empid int)create table #b(LEAVEID int)create table #c (empid int,LEAVEID int)insert into #a values(1)insert into #a values(2)insert into #b values(3)insert into #b values(4)insert into #c values(1,3)insert into #c values(2,4)insert into #c select a.* from (select * from #a,#b) a left join #c b on a.empid=b.empid and a.LEAVEID=b.LEAVEID where b.empid is nullselect * from #c empid LEAVEID ----------- ----------- 1 3 2 4 1 4 2 3(4 行受影响)
declare @table1 table(empid varchar(10))insert into @table1 select 'T001' union select 'T002' union select 'T003' union select 'T004' union select 'T005'declare @table2 table(leaveid varchar(10))insert into @table2 select 'L001' union select 'L002' union select 'L003' union select 'L004' union select 'L005'declare @table3 table(empid varchar(10),leaveid varchar(10))insert into @table3 select 'T001','L001' insert into @table3 select * from (select a.empid,b.leaveid from @table1 a,@table2 b) c where not exists(select 1 from @table3 d where d.leaveid=c.leaveid and d.empid=c.empid) select * from @table3
create table #a (empid int)create table #b(LEAVEID int)create table #c (empid int,LEAVEID int)insert into #a values(1)insert into #a values(2)insert into #b values(3)insert into #b values(4)insert into #c values(1,3)insert into #c values(2,4)insert into #c
select a.* from (select * from #a,#b) a left join #c b on a.empid=b.empid and a.LEAVEID=b.LEAVEID where b.empid is nullselect * from #c
empid LEAVEID
----------- -----------
1 3
2 4
1 4
2 3(4 行受影响)
declare @table1 table(empid varchar(10))insert into @table1
select 'T001'
union
select 'T002'
union
select 'T003'
union
select 'T004'
union
select 'T005'declare @table2 table(leaveid varchar(10))insert into @table2
select 'L001'
union
select 'L002'
union
select 'L003'
union
select 'L004'
union
select 'L005'declare @table3 table(empid varchar(10),leaveid varchar(10))insert into @table3
select 'T001','L001'
insert into @table3
select *
from
(select a.empid,b.leaveid
from @table1 a,@table2 b) c
where not exists(select 1 from @table3 d where d.leaveid=c.leaveid and d.empid=c.empid)
select * from @table3