从sql表中选择不同的当前列和上一列 [英] Select distinct current and previous columns from a sql table
问题描述
我有一个像这样的桌子
Id |Name |Status | Rate | Method |ModifiedTime |ModifiedBy
-----------------------------------------------------------------------------
1 |Recipe1 | 0 | 30 | xyz | 2016-07-26 14:55:57.977 | A
-------------------------------------------------------------------------------
2 |Recipe1 | 0 | 30 | abc | 2016-07-26 14:56:18.123 | A
--------------------------------------------------------------------------------
3 |Recipe1 | 1 | 30 | xyz | 2016-07-26 14:57:50.180 | b
我只想选择更改,并想显示以前的值以及当前由谁更改的值.最终结果将如下.我正在使用SQL Server 2014.
I would like to select only the changes and wanted to show what the value was previously and what it is currently accompanied by who changed it. The final outcome will be as follows. I am using SQL Server 2014.
Item | Before | After |ModifiedTime | ModifiedBy
-----------------------------------------------------------------------------
Method | xyz | Abc | 2016-07-26 14:56:18.123 | A
-------------------------------------------------------------------------------
Status | 0 | 1 | 2016-07-26 14:57:50.180 | b
--------------------------------------------------------------------------------
Method | Abc | xyz | 2016-07-26 14:57:50.180 | b
推荐答案
在没有动态SQL的情况下,这是我能做的最好的事情.
我假设您按Name
对更改进行分组.如果Name
可以更改,则应将其从分区中删除,并添加到items子查询和case
中.您可以在此处
without dynamic SQL this is the best I could do.
I'm assuming that you are grouping changes by Name
. If Name
can change it should be removed of the partition by and added to the items subquery and to the case
. You can try it here
select item,
case item
when 'Status' then cast(prevStatus as varchar)
when 'Rate' then cast(prevRate as varchar)
when 'Method' then prevMethod
end as Before,
case item
when 'Status' then cast(Status as varchar)
when 'Rate' then cast(Rate as varchar)
when 'Method' then Method
end as After,
ModifiedTime,
ModifiedBy
from (
select Status,
lag(Status) over (partition by Name order by id) prevStatus,
Rate,
lag(Rate) over (partition by Name order by id) prevRate,
Method,
lag(Method) over (partition by Name order by id) prevMethod,
ModifiedBy,
ModifiedTime
from t ) as t1 cross join (select 'Status' as item union all
select 'Rate' as item union all
select 'Method' as item) items
where (item = 'Status' and Status <> prevStatus)
or (item = 'Rate' and Rate <> prevRate)
or (item = 'Method' and Method <> prevMethod)
order by ModifiedTime
输出
item Before After ModifiedTime ModifiedBy
------ ------ ----- ----------------------- ----------
Method xyz abc 2016-07-26 14:56:18.123 A
Status 0 1 2016-07-26 14:57:50.180 b
Method abc xyz 2016-07-26 14:57:50.180 b
在第二行中,当您拥有A
时,将看到ModifiedBy为b
.我认为是错字.
On the second row you'll see that ModifiedBy is b
while you have A
. I think is a typo.
这篇关于从sql表中选择不同的当前列和上一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!