sql-server - 从审计表中查找更改
问题描述
我需要从此表中获取更新的列和数据详细信息。
预期结果是:
员工姓名:从 Jone 到 Jhone
员工年龄:25-20
员工姓名:从 Jhonny 到 Jone,员工年龄:从 18 到 25
我怎么能做到这一点?
更新
当记录更新时,我在审计表中插入两行。第一个记录Old(auditDataState)
与更新前的数据相同,第二个记录New(auditDataState)
与更新数据相同。
因此,每次更新在审计表中都有两条记录,分别是旧的和新的,具有旧值和新值。
我需要根据审计表从审计表中获取更新的数据updateColumns
。(我将哪些列存储在 updateColumns 列中)。
我不需要第一行Employee Name : from Jone to Jhone
。只需要识别更新的值。
解决方案
这将提供您问题中指定的结果,尽管它远非明智或可扩展的解决方案。如果可能的话,我建议你完全重新审视你的变更审计:
declare @EmpAudit table (
empID int
, empName varchar(50)
, empAge int
, auditDataState varchar(50)
, auditDMLAction varchar(50)
, auditUser varchar(50)
, auditDateTime datetime
, updateColumns varchar(50)
);
insert into @EmpAudit values
(1, 'Alex', 22, 'New', 'Insert','c@a.com',getdate(),''),
(2, 'Jhonny', 18, 'New', 'Insert','c@a.com',getdate()-0.5,''),
(2, 'Jhonny', 18, 'Old', 'Update','b@a.com',getdate()-1,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'New', 'Update','b@a.com',getdate()-1.5,'Employee Name, Employee Age'),
(2, 'Jone', 25, 'Old', 'Update','a@a.com',getdate()-2,'Employee Age'),
(2, 'Jone', 30, 'New', 'Update','a@a.com',getdate()-2.5,'Employee Age'),
(2, 'Jone', 30, 'Old', 'Update','a@a.com',getdate()-3,'Employee Age'),
(2, 'Jone', 20, 'New', 'Update','a@a.com',getdate()-3.5,'Employee Age'),
(2, 'Jone', 20, 'Old', 'Update','a@a.com',getdate()-4,'Employee Name'),
(2, 'Jhone', 20, 'New', 'Update','a@a.com',getdate()-4.5,'Employee Name');
with d as
(
select empID
,empName
,empAge
,auditDataState
,auditDMLAction
,auditUser
,auditDateTime
,updateColumns
,row_number() over (partition by empID order by auditDateTime) as rn
from @EmpAudit
)
select case when o.empName <> n.empName then 'Employee Name : from ' + o.empName + ' to ' + n.empName else '' end
+case when charindex(',',o.UpdateColumns) > 0 then ', ' else '' end
+case when o.empAge <> n.empAge then 'Employee Age : from ' + cast(o.empAge as varchar(3)) + ' to ' + cast(n.empAge as varchar(3)) else '' end as Change
from d as o
join d as n
on o.empID = n.empID
and o.updateColumns = n.updateColumns
and o.rn = n.rn+1
and n.auditDataState = 'New'
where o.auditDataState = 'Old';
输出:
Change
-----------------------------------------------------------------
Employee Name : from Jone to Jhone
Employee Age : from 30 to 20
Employee Age : from 25 to 30
Employee Name : from Jhonny to Jone, Employee Age : from 18 to 25
推荐阅读
- python - 在 vscode 中调试 python “包命令”
- nosql - 有没有办法索引 Oracle NoSQL 数据库中记录数组中的所有项目?
- php - 以两种不同的路径上传图片 Codeigniter
- c# - 设计者必须创建一个“EasyTabs.TitleBarTabs”类型的实例,但它不能,因为该类型被声明为抽象
- python - Django密码重置表单随机预填充电子邮件字段
- google-sheets - 谷歌表格:显示一周中某一天即将到来的日期
- python - 安装 autopy 时出现“SyntaxError: invalid syntax”
- nginx - 在从 https 到 http 的代理后面时强制使用 wss://
- python - 如何根据数据框标准制作脚本选择链接
- windows - 如何搜索/删除多个子文件夹?