本文介绍了如何在连续范围内分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我知道一些基本的 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()
这篇关于如何在连续范围内分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！