Question

我有一个名为Assignment的两列表。该表包含Person（nvarchar（20））和Day（date）的分配，如下所示：

Person      Day
------------------
John    2011-05-23
John    2011-05-24
John    2011-05-25
John    2011-05-27
John    2011-05-28
John    2011-05-29
Anna    2011-05-02
Anna    2011-05-03
Anna    2011-05-06

当有一系列相邻的日子时，我需要提取日期范围。每一行的输出都应该是一个单独的字符串值：

------------------
John    2011-05-23 - 2011-05-25
John    2011-05-27 - 2011-05-29
Anna    2011-05-02 - 2011-05-03
Anna    2011-05-06 - 2011-05-06

我试着分组，然后

   CAST(min(Day) AS VARCHAR) +   -   + CAST(max(Day) AS VARCHAR)

但是它不能正确地工作。

Any advice is welcome. Thanks.

Answer 1

declare @T table(Person nvarchar(20), [Day] date)

insert into @T values
( John ,     2011-05-23 ),
( John ,     2011-05-24 ),
( John ,     2011-05-25 ),
( John ,     2011-05-27 ),
( John ,     2011-05-28 ),
( John ,     2011-05-29 ),
( Anna ,     2011-05-02 ),
( Anna ,     2011-05-03 ),
( Anna ,     2011-05-06 )


;WITH cte AS
(
SELECT *,
        DATEDIFF(DAY,0,[Day])-
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY [Day]) AS Grp
FROM @T  
)
SELECT Person, 
       MIN([Day]) AS DateFrom, 
       MAX([Day]) AS DateTo  
FROM cte 
GROUP BY Person, Grp

友情链接