查找行更改（带有重复项）和输出到表

I posted a question (Find Row Changes and Output to Table) regarding determining the rows in a table where a column values changes, and outputting the results to a table. The successful query (with help from the accepted answer, of course) was as follows:

我发布了一个问题（查找行更改和输出到表），关于确定列值更改的表中的行，并将结果输出到表。成功的查询（当然，在接受的答案的帮助下）如下：

;with x as (
select *, row_number() over(partition by ModelNum order by transdate) rn
from table
)
select ModelNum, TransDate as 'StartTime', lead(TransDate) over (order by TransDate) as 'EndTime' from x where rn = 1

My issue, though, is that the original table has duplicates for ModelNum, but the code that I'm using from the solution doesn't account for them. It just scans through the table and looks for the first and last value. In other words, if the original table is like this:

不过，我的问题是原始表有ModelNum的重复项，但我从解决方案中使用的代码没有考虑到它们。它只扫描表格并查找第一个和最后一个值。换句话说，如果原始表是这样的：

id     TransDate            PartType
======================================
1     2016-06-29 10:23:00   A
2     2016-06-29 10:30:00   A
3     2016-06-29 10:32:00   C
4     2016-06-29 10:33:00   C
5     2016-06-29 10:35:00   C
6     2016-06-29 10:39:00   A
7     2016-06-29 10:41:00   A
8     2016-06-29 10:43:00   B

How do I output the results to a table, so that duplicates are listed too, like so:

如何将结果输出到表中，以便列出重复项，如下所示：

PartType  StartTime             EndTime       
=======================================================
A         2016-06-29 10:23:00   2016-06-29 10:32:00
C         2016-06-29 10:32:00   2016-06-29 10:39:00
A         2016-06-29 10:39:00   2016-06-29 10:43:00
B         2016-06-29 10:43:00   NULL

Also, in the original question, I was using SQLServer 2008, but now I am using 2014.

另外，在最初的问题中，我使用的是SQLServer 2008，但现在我正在使用2014。

2 个解决方案

#1

This is a gap and islands problem. I like to solve it with the difference of row numbers approach:

这是一个缺口和岛屿问题。我喜欢用行数方法的不同来解决它：

with x as (
      select t.*,
             row_number() over (order by transdate) as seqnum,
             row_number() over (partition by ModelNum order by transdate) as seqnum_m
      from table t
     )
select ModelNum, min(TransDate) as StartTime, max(TransDate) as EndTime
from x 
group by ModelNum, (seqnum - seqnum_m);

How this works is a little tricky. If you run the CTE and stare at the results, you will probably "get" how the difference finds the groups you are looking for.

这是如何工作有点棘手。如果您运行CTE并盯着结果，您可能会“获得”差异如何找到您要查找的组。

#2

If I understand your question and all you want to do is include the duplicates, you can just remove the where clause "where rn = 1" from the query.

如果我理解你的问题并且你想要做的就是包含重复项，你可以从查询中删除where子句“where rn = 1”。

#1