环境：MSSqlsever 2000+win2003。
问题：想拿表：发放金额 与 表：发放金额备份，检测：姓名 工资 奖金 津贴字段的值是否一致，把不一致的记录查找出来，目的是想看数据是否被篡改过？（两表可能ID字段做为唯一关联字段）
表结构如下：
表：发放金额
ID 姓名 工资 奖金 津贴
1 张三 100 100 180
2 李四 150 160 100
3 王五 100 100 130
4 张三 100 140 100
表：发放金额备份
ID 姓名 工资 奖金 津贴
1 张三 100 100 180
2 李四 150 160 100
3 王五 100 130 130
4 张三 100 140 100
------解决方案--------------------
select a.*,b.* from 发放金额 a inner join 发放金额备份 b on a.id=b.id
where not(a.姓名=b.姓名 and a.工资=b.工资 and a.奖金=b.奖金 and a.津贴=b.津贴)
------解决方案--------------------
with A as (
select 1 as id,'张三' as name ,150 as pay union all
select 2 as id,'李四' as name ,150 as pay union all
select 3 as id,'王五' as name ,150 as pay union all
select 4 as id,'赵六' as name ,150 as pay union all
select 5 as id,'刘七' as name ,150 as pay
),B as
(select 1 as id,'张三' as name ,150 as pay union all
select 2 as id,'李四' as name ,160 as pay union all
select 3 as id,'王五' as name ,150 as pay union all
select 4 as id,'赵六' as name ,150 as pay
)
select A.*,B.*
from A left join B on A.id = B.id
where not exists (select 1 from B where A.id = B.id and A.name=B.name and A.pay=B.pay)
------解决方案--------------------
select a.*,b.* from 发放金额 a,发放金额备份 b where a.id=b.id and (a.姓名<>b.姓名 or a.工资<>b.工资 or a.奖金<>b.奖金 or a.津贴<>b.津贴)
