**编辑:我们当前的服务器是SQL 2008 R2,因此LAG / LEAD函数将不起作用。
我正在尝试在一个表中获取多个数据流,并将它们组合成1个数据流。给定下面的3个数据流,我希望最终结果为1个流,优先选择状态“ on”。递归似乎是最好的选择,但是到目前为止,我没有运气来完成一个查询,该查询可以满足我的需求。
CREATE TABLE #Dates(
id INT IDENTITY,
status VARCHAR(4),
StartDate Datetime,
EndDate Datetime,
booth int)
INSERT #Dates
VALUES
( 'off','2015-01-01 08:00','2015-01-01 08:15',1),
( 'on','2015-01-01 08:15','2015-01-01 09:15',1),
( 'off','2015-01-01 08:50','2015-01-01 09:00',2),
( 'on','2015-01-01 09:00','2015-01-01 09:30',2),
( 'off','2015-01-01 09:30','2015-01-01 09:35',2),
( 'on','2015-01-01 09:35','2015-01-01 10:15',2),
( 'off','2015-01-01 09:30','2015-01-01 10:30',3),
( 'on','2015-01-01 10:30','2015-01-01 11:00',3)
status StartDate EndDate
---------------------------
off 08:00 08:15
on 08:15 09:15
off 08:50 09:00
on 09:00 09:30
off 09:30 09:35
on 09:35 10:15
off 09:30 10:30
on 10:30 11:00
最终结果:
status StartDate EndDate
---------------------------
off 8:00 8:15
on 8:15 9:15
on 9:15 9:30
off 9:30 9:35
on 9:35 10:15
off 10:15 10:30
on 10:30 11:00
本质上,任何时候只要状态为“开”,它都应覆盖所有并发的“关”状态。
Source:
|----off----||---------on---------|
|---off--||------on----||---off---||--------on------|
|--------------off------------------||------on------|
Result (Either result would work):
|----off----||----------on--------||---on---||---off---||--------on------||-off--||------on------|
|----off----||----------on------------------||---off---||--------on------||-off--||------on------|
这是我能够弄清楚的2008年最简单的版本:
; with Data (Date) as (
select StartDate from Dates
union
select EndDate from Dates),
Ranges (StartDate, Status) as (
select D.Date, D2.Status
from Data D
outer apply (
select top 1 D2.Status
from Dates D2
where D2.StartDate <= D.Date and D2.EndDate > D.Date
order by case when Status = 'on' then 1 else 2 end
) D2)
select R.StartDate,
(select min(D.Date) from Data D where D.Date > R.StartDate) as EndDate,
Status
from Ranges R
order by R.StartDate
即使状态与先前相同,它也会从每个起点/终点开始返回新行。没有找到任何简单的方法来组合它们。
编辑:将第一个CTE更改为此将合并行:
; with Data (Date) as (
select distinct StartDate from Dates D1
where not exists (Select 1 from Dates D2
where D2.StartDate < D1.StartDate and D2.EndDate > D1.StartDate and
Status = 'on')
union
select distinct EndDate from Dates D1
where not exists (Select 1 from Dates D2
where D2.StartDate < D1.EndDate and D2.EndDate > D1.EndDate and
Status = 'on')
),
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句