問題描述

我有一個看起來像這樣的表格:

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    |
+------------+------+

我希望能夠將其變成以下內容:

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    |
+-------------------------+------+

該代碼將僅查找連續日期的最小值和最大值，使用 名稱 列對結果進行分組，然后將最小值和最大值日期列為一列中的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.

我在嘗試僅列出連續日期時遇到問題.請注意，上面的第三個條目有自己的條目，因為它與前面條目中A"的日期范圍不連續.

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.

請注意:這是特定于 SQL Server 2008 的，它不允許使用 LAG 函數.

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

編輯 2:McNets 提供的原始答案在 SQL Server 2012 上運行良好.我已將其包含在此處，因為如果您有 SQL Server 2012 以后會更好.

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;

推薦答案

首先，我在整個表格中添加了一個行號.

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
)

然后我將這個表與它本身結合起來只是為了計算日期之間的天數.

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
)

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

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

島嶼.通過將名稱添加到計算的天數中.

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   |
+---------------------+------+---------+

第二部分:獲取每個島嶼的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

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