如何在连续范围内分组 [英] How do I group on continuous ranges

问题描述

我知道一些基本的 SQL，但我无法理解这一点.我看着高低，但没有骰子.我需要查看以下数据，我可以在应用层代码中做到这一点.但不幸的是，对于这个特殊的，代码必须放在数据层.

I know some basic SQL, but this one is beyond me. I have looked high and low but no dice. I need a view of the following data, I can do this in the application layer code. But unfortunately for this particular one, the code must be put in the data layer.

我正在使用 T-SQL.

I am using T-SQL.

表格

Date      Crew       DayType
01-02-11  John Doe  SEA  
02-02-11  John Doe  SEA  
03-02-11  John Doe  SEA  
04-02-11  John Doe  HOME  
05-02-11  John Doe  HOME  
06-02-11  John Doe  SEA 

我需要这样的视图

DateFrom  DateTo    Name      DayType
01-02-11  03-02-11  John Doe  SEA
04-02-11  05-02-11  John Doe  HOME
06-02-11  06-02-11  John Doe  SEA

不幸的是，应用层需要基表才能采用所示格式.这可以在查询中执行吗?

Unfortunately, the base table is required for the application layer to be in the format shown. Is this possible to do in a query?

推荐答案

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd,
                ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn
        FROM    mytable
        )
SELECT  MIN([date]), MAX([date]), crew AS name, dayType
FROM    q
GROUP BY
        crew, dayType, rnd - rn

您可能对本文感兴趣:

• 事情 SQL 需要:SERIES()

这篇关于如何在连续范围内分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！