显示同一表中行之间的差异

Question

我有一个名为Employee_audit的表，其模式如下，

emp_audit_id100010003000

Answer 1

lead和lag函数是用来帮助你的。

"diffs"计算需要查找diff到

的每个列的差异

with diffs as (
select 'name' colName, emp_audit_id, eid, lag(name, 1, null) over (partition by eid order by emp_audit_id) oldValue, name newValue
from some_table
union all
select 'salary', emp_audit_id, eid, cast(lag(salary, 1, null) over (partition by eid order by emp_audit_id) as varchar), cast(salary as varchar) newValue
from some_table
)
select * 
from diffs 
where oldValue <> newValue or oldValue is null 
order by emp_audit_id, eid

Answer 2

如果您在CTE中为每一行提供行号，然后连接到下一行，您可以比较旧值和新值。合并两个不同的列名有点笨拙，但是，如果您需要一个更健壮的解决方案，您可以考虑旋转数据。

显然，你还必须将所有值转换为通用的数据类型，例如字符串。

declare @Test table (emp_audit_id int, eid int, [name] varchar(32), salary money);
insert into @Test (emp_audit_id, eid, [name], salary)
values
(1, 1, 'Daniel', 1000),
(2, 1, 'Dani', 1000),
(3, 1, 'Danny', 3000);
with cte as (
select emp_audit_id, eid, [name], salary
, row_number() over (partition by eid order by emp_audit_id) rn
from @Test
)
select C.emp_audit_id, 'name' columnName, P.[Name] oldValue, C.[name] newValue
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.[name],'') != coalesce(P.[Name],'')
union all
select C.emp_audit_id, 'salary' columnName, convert(varchar(21),P.salary), convert(varchar(21),C.salary)
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.salary,0) != coalesce(P.salary,0)
order by C.emp_audit_id, columnName;

的回报:

tbody> <<tr>12

emp_audit_id	columnName	oldValue	newValue
1	名称	空	丹尼尔
	工资	空	1000.00
	名称	丹尼尔	达尼
3	名称	达尼	丹尼
3	工资	1000.00	3000.00