17,377
社区成员
发帖
与我相关
我的任务
分享
员工原表:
id name depNo
1 张三 5
2 李四 5
3 王五 6
数据改动后的表:
id name depNo
1 张三 5
2 李四 6
3 王五 6
4 赵六 5
5 田七 6
(
SELECT * FROM new_table
MINUS
SELECT * FROM old_table
)
UNION ALL
(
SELECT * FROM old_table t1
WHERE NOT EXISTS (SELECT NULL FROM new_table t2 WHERE t1.ID = t2.id)
)
SELECT *
FROM new_table
MINUS
SELECT *
FROM old_table
select a.*,'新增' flag from new_table a
where not exists
(select 1 from old_table b where a.id=b.id)
union all
select a.*,'删除' flag from old_table a
where not exists
(select 1 from new_table b where a.id=b.id)
union all
select a.*,'修改' flag from old_table a,
new_table b where a.id=b.id and (a.name<>b.name or
a.depno<>b.depno)