解决方案: |
参照执行如下脚本即可:
1、检查分析
select scardnum,max(sid) as sid
into tempdb..a
from fa_DeptScale
group by scardnum
select scardnum,max(scardid) as scardid
into tempdb..b
from fa_cards
group by scardnum
select *
from fa_DeptScale
where sid in ( select sid from tempdb..a )
order by sid
select *
from fa_cards
where scardid in (select scardid from tempdb..b )
order by scardid
select d.*
from fa_DeptScale c
inner join (select * from fa_cards where scardid in (select scardid from tempdb..b )) d
on c.scardnum=d.scardnum and c.sdeptnum<>d.sdeptnum
where c.sid in ( select sid from tempdb..a )
2、--所差金额
select sum(dblDepr3)
from fa_DeprTransactions
where scardnum in (select d.scardnum from fa_DeptScale c
inner join (select * from fa_cards where scardid in (select scardid from tempdb..b )) d
on c.scardnum=d.scardnum and c.sdeptnum<>d.sdeptnum
where c.sid in ( select sid from tempdb..a ) )
3、--修复fa_DeptScale与fa_cards相同卡片部门不一致的记录
update c
set c.sdeptnum=d.sdeptnum
from fa_DeptScale c
inner join (select * from fa_cards where scardid in (select scardid from tempdb..b )) d
on c.scardnum=d.scardnum and c.sdeptnum<>d.sdeptnum
where c.sid in ( select sid from tempdb..a )
drop table tempdb..a
drop table tempdb..b
注意做好数据备份!! |