从sql表中选择不同的当前列和上一列

从sql表中选择不同的当前列和上一列

如果没有动态SQL，这是我所能做的最好的事情。我假设您将更改分组为

Name

。如果

Name

可以更改，则应通过删除该分区，并将其添加到items子查询和

case

。你可以在这里尝试

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) itemswhere (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

在第二行中，您将看到ModifiedBy

b

在您拥有的同时

A

。我认为是错字。