問(wèn)題描述

我有一個(gè)看起來(lái)像這樣的表格:

I have table that looks like this:

+------------+------+
|    Date    | Name |
+------------+------+
| 2017-01-07 | A    |
| 2017-01-08 | A    |
| 2017-01-09 | A    |
| 2017-01-12 | A    |
| 2017-01-07 | B    |
| 2017-01-08 | B    |
| 2017-01-09 | B    |
+------------+------+

我希望能夠?qū)⑵渥兂梢韵聝?nèi)容:

I would like to be able to turn it into the following:

+-------------------------+------+
|       Date Range        | Name |
+-------------------------+------+
| 2017-01-07 - 2017-01-09 | A    |
| 2017-01-07 - 2017-01-09 | B    |
| 2017-01-12              | A    |
+-------------------------+------+

該代碼將僅查找連續(xù)日期的最小值和最大值，使用 名稱(chēng) 列對(duì)結(jié)果進(jìn)行分組，然后將最小值和最大值日期列為一列中的to 和 from"字符串.

The code would find the minimum and maximum of consecutive dates only, group the results using the Name column and then list the minimum and maximum dates as a 'to and from' string in one column.

我在嘗試僅列出連續(xù)日期時(shí)遇到問(wèn)題.請(qǐng)注意，上面的第三個(gè)條目有自己的條目，因?yàn)樗c前面條目中A"的日期范圍不連續(xù).

I'm having problems trying to list consecutive dates only. Note that the third entry above gets its own entry because it is not consecutive with the date range for 'A' in the earlier entry.

請(qǐng)注意:這是特定于 SQL Server 2008 的，它不允許使用 LAG 函數(shù).

Please note: This is specific to SQL Server 2008, which does not allow use of the LAG function.

編輯 2:McNets 提供的原始答案在 SQL Server 2012 上運(yùn)行良好.我已將其包含在此處，因?yàn)槿绻?SQL Server 2012 以后會(huì)更好.

EDIT 2: The original answer supplied by McNets worked fine on SQL Server 2012. I've included it here as it's better if you have SQL Server 2012 onwards.

;WITH CalcDiffDays AS
(
    SELECT Date, Name,
    CONCAT (Name, CAST(DATEDIFF(DAY, LAG(Date, 1, Date - 1) OVER (PARTITION BY Name ORDER BY Name, Date), Date) AS VARCHAR(10))) AS NumDays
    FROM @tmpTable
)
SELECT CONCAT(CONVERT(VARCHAR(20), MIN(Date), 102), ' - ', CONVERT(VARCHAR(20), MAX(Date), 102)) AS [Data Range], Name
FROM CalcDiffDays
GROUP BY NumDays, Name;

推薦答案

首先，我在整個(gè)表格中添加了一個(gè)行號(hào).

First I've added a row number to the whole table.

WITH RowN AS
(
    SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
    FROM #T
)

然后我將這個(gè)表與它本身結(jié)合起來(lái)只是為了計(jì)算日期之間的天數(shù).

Then I've joined this table with itself just to calculate days between dates.

,CalcDiffDays AS
(
    SELECT RowN.Date, RowN.Name,
        ISLAND = RowN.Name + 
        CASE  
           WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
           ELSE '1'
        END
    FROM RowN
         LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)

差距.同名的連續(xù)日期之間相差多少天.

GAPS. How many days between consecutive dates of the same name.

島嶼.通過(guò)將名稱(chēng)添加到計(jì)算的天數(shù)中.

ISLANDS. By adding the name to the calculated days.

+---------------------+------+---------+
|         Date        | Name | NumDays |
+---------------------+------+---------+
| 07.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 08.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 09.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 12.01.2017 00:00:00 |   A  |    A3   |
+---------------------+------+---------+
| 07.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+
| 08.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+
| 09.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+

第二部分:獲取每個(gè)島嶼的MIN和MAX日期.

The second part: get the MIN and MAX Date of each island.

WITH RowN AS
(
    SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
    FROM #T
)
,CalcDiffDays AS
(
    SELECT RowN.Date, RowN.Name,
        ISLAND = RowN.Name + 
        CASE  
           WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
           ELSE '1'
        END
    FROM RowN
         LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)    
SELECT CONVERT(VARCHAR(20), MIN(Date), 102) + ' - ' + CONVERT(VARCHAR(20), MAX(Date), 102) AS [Data Range], Name
FROM CalcDiffDays
GROUP BY ISLAND, Name
ORDER BY MIN(Date);

+-------------------------+------+
|        Data Range       | Name |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 |   A  |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 |   B  |
+-------------------------+------+
| 2017.01.12 - 2017.01.12 |   A  |
+-------------------------+------+

可以在這里查看:http://rextester.com/MHLEEJ50479

這篇關(guān)于在自定義列中顯示日期范圍 - 差距和孤島的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！