如何分组连续范围 [英] 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

不幸的是，应用程序层必须使用show格式的基本表.这可以在查询中完成吗?

Unfortunately the base table is required for application layer to be in the format show. Is this possible to do in 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

您可能会对本文感兴趣:

This article may be of interest to you:

• SQL需求:SERIES()

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