表TAB1
period(月) fnum 其他字段
1 1
1 2
1 3
2 1
2 2
3 1
3 2
4 1
4 2
4 3
..表TAB1
period fnum 其他字段
1 4
1 2
1 3
2 5
2 6
3 1
3 3
4 1
4 2
4 4
..表TAB1和表TAB2的关系是
表TAB1中period和和fnum构成联合主键,其外键是
表tab2中的period和fnum.
现在由于数据库损坏,表tab1和tab2中的关系消失,
造成表TAB2中1月份有些数据不在表tab1中的fnum中(如fnum=4)
2月份有些数据不在表tab1中的fnum中(如fnum=5,6)
3月份有些数据不在表tab1中的fnum中(如fnum=3)
4月份有些数据不在表tab1中的fnum中(如fnum=4)
...
现在想把表tab2中和tab1中相同月份但是tab2中fnum不在tab1中的fnum数字找出来,用一个SQL,谢谢!
insert into tbA select
1 , 1 union select
1 , 2 union select
1 , 3 union select
2 , 1 union select
2 , 2 union select
3 , 1 union select
3 , 2 union select
4 , 1 union select
4 , 2 union select
4 , 3
insert into tbB select1 , 4 union select
1 , 2 union select
1 , 3 union select
2 , 5 union select
2 , 6 union select
3 , 1 union select
3 , 3 union select
4 , 1 union select
4 , 2 union select
4 , 4select tb.period,tb.fnum
from (select a.period,a.fnum,b.period as flag
from tbB a left join tbA b on (a.period=b.period and a.fnum=b.fnum)) tb
where tb.flag is null
from tabA a
full join tabB b on a.period=b.period and a.fnum=b.fnum
declare @tab1 table (period int , fnum int )
insert into @tab1 select
1 , 1 union select
1 , 2union select
1 , 3union select
2 , 1union select
2 , 2union select
3 , 1union select
3 , 2union select
4 , 1union select
4 , 2union select
4 , 3
declare @tab2 table (period int , fnum int )
insert into @tab2 select1 , 4 union select
1 , 2 union select
1 , 3 union select
2 , 5 union select
2 , 6 union select
3 , 1 union select
3 , 3 union select
4 , 1 union select
4 , 2 union select
4 , 4
--处理语句
select *
from @tab2 a
where not exists(select 1 from @tab1 where period=a.period and fnum=a.fnum)/*结果
1 4
2 5
2 6
3 3
4 4*/
where
exists(select * from tab1 where period =a.period)
and
not exists(select * from tab1 where period =a.period and fnum = a.fnum)
where b.funm not in (select fnum from tab1 where period = b.period)