SQL Server 2008，临时表，游标

Question

我已经在这方面工作了一段时间。我想知道如何获取这个表格：

id       open_dt             ops_hrs
1        10/31/2011          7:00AM - 5:30PM
2        11/1/2011           7:00AM - 5:00PM
3        11/2/2011           7:00AM - 5:00PM
4        11/3/2011           7:00AM - 5:00PM
5        11/6/2011           7:00AM - 7:00PM
6        11/8/2011           7:00AM - 5:00PM

要看起来像这个表格：

max_date          min_date     ops_hrs
10/31/2011        10/31/2011   7:00AM - 5:30PM
11/1/2011         11/3/2011    7:00AM - 5:00PM
11/6/2011         11/6/2011    7:00AM - 7:00PM
11/8/2011         11/8/2011    7:00AM - 5:00PM

我尝试使用游标，但它并不是必需的。此外，它必须被分组。只要连续的日子中断，就会出现一个新的分组。任何帮助都将不胜感激。

此查询将生成上述示例数据。

;
WITH pdog (id, open_dt,ops_hrs) AS
(
SELECT 1, CAST('10/31/2011' AS datetime),  '7:00AM - 5:30PM'
UNION ALL SELECT 2, CAST('11/1/2011' AS datetime),'7:00AM - 5:00PM'
UNION ALL SELECT 3, CAST('11/2/2011' AS datetime),'7:00AM - 5:00PM'
UNION ALL SELECT 4, CAST('11/3/2011' AS datetime),'7:00AM - 5:00PM'
UNION ALL SELECT 5, CAST('11/6/2011' AS datetime),'7:00AM - 7:00PM'
UNION ALL SELECT 6, CAST('11/8/2011' AS datetime),'7:00AM - 5:00PM'
)
SELECT * FROM pdog 

- pdog

---

如果有一行 11/4/2011 7:00AM - 6:00PM 会发生什么？ - Aaron Bertrand

这将是另一组，最小日期和最大日期将为2011年11月4日。 - pdog

因此，分组是根据ops_hrs的值以及前一天是否为前一天进行的。 - Aaron Bertrand

是的 -- 连续的天数被分组在一起 - pdog

我找到了两篇对我在SQL Server中制作光标和临时表非常有帮助的文章。点击此处阅读infoA2z.com上关于SQL Server中临时表的文章 点击此处阅读infoA2z.com上关于SQL Server中光标的文章 - Rakesh Dhiman

Answer 1

;WITH    CTE
          AS ( SELECT   * ,
                        DATEDIFF(DAY, 0, open_dt) -  ROW_NUMBER() OVER 
                              ( PARTITION BY ops_hrs ORDER BY open_dt ) AS Grp
               FROM     @x
             )
    SELECT  
            MIN(open_dt) AS min_date ,
            MAX(open_dt) AS max_date ,
            ops_hrs
    FROM    CTE
    GROUP BY ops_hrs ,
            Grp
    ORDER BY min_date

- Martin Smith

---

非常简洁整洁，但是我第一眼看到DATEDIFF - ROW_NUMBER()的逻辑时真的很难理解。 - Aaron Bertrand

Answer 2

肯定比@Martin的解决方案稍微复杂一些，但是我应该至少得到一分，因为他使用了我的@x表格 - 所以他的解决方案看起来更加整洁。 :-)

DECLARE @x TABLE(id INT IDENTITY(1,1), open_dt DATE, ops_hrs VARCHAR(32));

INSERT @x(open_dt, ops_hrs) VALUES
    ('2011-10-31', '7:00AM - 5:30PM'),
    ('2011-11-01', '7:00AM - 5:00PM'),
    ('2011-11-02', '7:00AM - 5:00PM'),
    ('2011-11-03', '7:00AM - 5:00PM'),
    ('2011-11-06', '7:00AM - 7:00PM'),
    ('2011-11-08', '7:00AM - 5:00PM');

;WITH d AS
(
    SELECT open_dt, ops_hrs, max_date = COALESCE((SELECT MAX(open_dt)
        FROM @x AS b WHERE b.open_dt > a.open_dt 
        AND NOT EXISTS (SELECT 1 FROM @x AS c
          WHERE c.open_dt >= a.open_dt 
          AND   c.open_dt <  b.open_dt 
          AND   c.ops_hrs <> b.ops_hrs)), open_dt)
    FROM @x AS a
)
SELECT 
    min_date = MIN(open_dt),
    max_date, 
    ops_hrs
FROM d
    GROUP BY max_date, ops_hrs
    ORDER BY min_date;

- Aaron Bertrand

---

除了变量名之外，那个脚本看起来非常像我电脑屏幕上的内容。 - billinkc