查找行更改并输出到表
问题描述：
我有一个具有以下结构的SQL Server表:
I have an SQL Server table of the following structure:
id TransDate PartType
======================================
1 2016-06-29 10:23:00 A1
2 2016-06-29 10:30:00 A1
3 2016-06-29 10:32:00 A2
4 2016-06-29 10:33:00 A2
5 2016-06-29 10:35:00 A2
6 2016-06-29 10:39:00 A3
7 2016-06-29 10:41:00 A4
我需要一个SELECT语句，该语句将输出一个表，该表查找PartType中的更改，并且看起来像这样(出于SSRS的目的):
I need a SELECT statement that will output a table that finds the changes in PartType, and that looks like this (for SSRS purposes):
PartType StartTime EndTime
=======================================================
A1 2016-06-29 10:23:00 2016-06-29 10:32:00
A2 2016-06-29 10:32:00 2016-06-29 10:39:00
A3 2016-06-29 10:39:00 2016-06-29 10:41:00
A4 2016-06-29 10:41:00 NULL
请注意，除非它是表中的第一条记录，否则StartTime总是从最后一个EndTime开始.
Note that the StartTime always picks up from the last EndTime, unless it's the first record in the table.
我的SELECT语句应该是什么?我似乎无法获得预期的结果.
What should my SELECT statement be? I can't seem to get the intended results.
我正在使用SQL Server 2008 R2；我应该指定这个.
I'm using SQL Server 2008 R2; I should've specified that.
答
在SQL Server 2012及更高版本中，您可以使用以下方法:
With SQL Server 2012 and later, you can use this:
declare @t table (id int, transdate datetime2(0), parttype char(2))
insert @t
values
(1, '2016-06-29 10:23:00', 'A1'),
(2, '2016-06-29 10:30:00', 'A1'),
(3, '2016-06-29 10:32:00', 'A2'),
(4, '2016-06-29 10:33:00', 'A2'),
(5, '2016-06-29 10:35:00', 'A2'),
(6, '2016-06-29 10:39:00', 'A3'),
(7, '2016-06-29 10:41:00', 'A4')
;with x as (
select *, row_number() over(partition by parttype order by transdate) rn
from @t
)
select parttype, transdate starttime, lead(transdate) over (order by transdate) from x where rn = 1