我知道一些基本的SQL,但这一点我无法理解。我看了又看又看,但没有骰子。我需要以下数据的视图,我可以在应用层代码中做到这一点。但不幸的是,对于这个特定的代码,代码必须放在数据层中。
我使用的是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不幸的是,要使应用层采用所示的格式,基表是必需的。这可以在查询中实现吗?
发布于 2011-04-14 19:36:06
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这篇文章可能会让您感兴趣:
发布于 2011-04-14 21:46:31
WITH grouped AS (
SELECT
*,
grp = DATEDIFF(day, 0, Date) -
ROW_NUMBER() OVER (PARTITION BY Crew, DayType ORDER BY Date)
FROM @testtable
)
SELECT
DateFrom = MIN(Date),
DateTo = MAX(Date),
Name = Crew,
DayType
FROM grouped
GROUP BY Crew, DayType, grp;基本上,与Quassnoi的解决方案相同,但使用较少的一个ROW_NUMBER会产生更好的执行计划。
发布于 2011-04-14 19:35:41
SELECT MIN(Date) AS DateFrom,MAX(Date) AS DateTo, Crew, DayType FROM yourTableName GROUP BY Crew, DayTypehttps://stackoverflow.com/questions/5662545
复制相似问题