代码之家 › 专栏 › 技术社区 › automatic

围绕差距进行SQL分组

tsql sql-server-2005 sql-server sql

automatic · 技术社区 · 16 年前

在SQL Server 2005中,我有一个表,其中的数据看起来像这样:

WTN------------Date  
555-111-1212  2009-01-01  
555-111-1212  2009-01-02  
555-111-1212  2009-01-03  
555-111-1212  2009-01-15  
555-111-1212  2009-01-16  
212-999-5555  2009-01-01  
212-999-5555  2009-01-10  
212-999-5555  2009-01-11

我想从中提取WTN、Min(日期)、Max(日期) 也打破

WTN------------ MinDate---- MaxDate  
555-111-1212   2009-01-01  2009-01-03  
555-111-1212   2009-01-15  2009-01-16  
212-999-5555   2009-01-01  2009-01-01  
212-999-5555   2009-01-10  2009-01-11

如何在SQL选择/分组依据中执行此操作?
这可以在没有表格或列表的情况下完成吗?这些表格或列表列出了我想在(此处为日期)中识别的空白值?

3 回复 | 直到 16 年前

Aaron Bertrand 16 年前

DECLARE @wtns TABLE
(
    WTN    CHAR(12),
    [Date] SMALLDATETIME
);

INSERT @wtns(WTN, [Date])
          SELECT '555-111-1212','2009-01-01'
UNION ALL SELECT '555-111-1212','2009-01-02'
UNION ALL SELECT '555-111-1212','2009-01-03'
UNION ALL SELECT '555-111-1212','2009-01-15'
UNION ALL SELECT '555-111-1212','2009-01-16'
UNION ALL SELECT '212-999-5555','2009-01-01'
UNION ALL SELECT '212-999-5555','2009-01-10' 
UNION ALL SELECT '212-999-5555','2009-01-11';

WITH x AS
(
    SELECT
        [Date],
        wtn,
        part = DATEDIFF(DAY, 0, [Date]) 
        + DENSE_RANK() OVER
        (
            PARTITION BY wtn
            ORDER BY [Date] DESC
        )
    FROM @wtns
)
SELECT 
    WTN, 
    MinDate = MIN([Date]),
    MaxDate = MAX([Date])
FROM
    x
GROUP BY 
    part,
    WTN
ORDER BY
    WTN DESC,
    MaxDate;

Erwin Smout 16 年前

不要指望任何SQL系统真的能帮助你解决这些问题。

Cade Roux 16 年前

GROUP BY ,通过检测边界:

WITH    Boundaries
      AS (
          SELECT    m.WTN
                   ,m.Date
                   ,CASE WHEN p.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsStart
                   ,CASE WHEN n.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsEnd
          FROM      so1590166 AS m
          LEFT JOIN so1590166 AS p
                    ON p.WTN = m.WTN
                       AND p.Date = DATEADD(d, -1, m.Date)
          LEFT JOIN so1590166 AS n
                    ON n.WTN = m.WTN
                       AND n.Date = DATEADD(d, 1, m.Date)
          WHERE     p.Date IS NULL
                    OR n.Date IS NULL
         )
SELECT  l.WTN
       ,l.Date AS MinDate
       ,MIN(r.Date) AS MaxDate
FROM    Boundaries l
INNER JOIN Boundaries r
        ON r.WTN = l.WTN
           AND r.Date >= l.Date
           AND l.IsStart = 1
           AND r.IsEnd = 1
GROUP BY l.WTN
       ,l.Date