27,580
社区成员
发帖
与我相关
我的任务
分享把连续时间段的数据合并一条
有数据表T，内容如下：
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 2
100 1 2 3
100 1 3 4
100 1 5 6
根据相同的EndTime、StartTime，合并为：
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 4
100 1 5 6
请大家帮忙！
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 2
100 1 2 3
100 1 3 4
100 1 5 6
根据相同的EndTime、StartTime，合并为：
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 4
100 1 5 6
请大家帮忙！
...全文
请发表友善的回复…
发表回复
發糞塗牆 2014-07-28
- 打赏
- 举报
这一段要一起执行
with v as
(select a.Card_ID,a.Locator_ID,a.StartTime,a.EndTime,a.StartTime 'x'
from T a
where not exists(select 1
from T b
where b.Card_ID=a.Card_ID and b.Locator_ID=a.Locator_ID
and b.EndTime=a.StartTime)
union all
select d.Card_ID,d.Locator_ID,d.StartTime,d.EndTime,c.x
from v c
inner join T d on c.Card_ID=d.Card_ID and c.Locator_ID=d.Locator_ID
and c.EndTime=d.StartTime)
select Card_ID,Locator_ID,min(StartTime) 'StartTime',max(EndTime) 'EndTime'
from v
group by Card_ID,Locator_ID,x發糞塗牆 2014-07-28
- 打赏
- 举报
要一次执行，不能拿出一部分执行
小白索引 2014-07-28
- 打赏
- 举报
在SQL2008R2,SQL2005环境测试均正常,请问LZ是什么环境?[/quote]
select d.Card_ID,d.Locator_ID,d.StartTime,d.EndTime,c.x
from v c
inner join T d on c.Card_ID=d.Card_ID and c.Locator_ID=d.Locator_ID and c.EndTime=d.StartTime)
小白索引 2014-07-28
- 打赏
- 举报
在SQL2008R2,SQL2005环境测试均正常,请问LZ是什么环境?[/quote]
sql 2008 r2
我再检查一下
唐诗三百首 2014-07-28
- 打赏
- 举报
在SQL2008R2,SQL2005环境测试均正常,请问LZ是什么环境?
小白索引 2014-07-28
- 打赏
- 举报
谢谢你~但是c.x无效？
Tiger_Zhao 2014-07-28
- 打赏
- 举报
--用函数查找的方式
CREATE TABLE T (
Card_ID int,
Locator_ID int,
StartTime int,
EndTime int
)
INSERT INTO T VALUES(100,1,1,2)
INSERT INTO T VALUES(100,1,2,3)
INSERT INTO T VALUES(100,1,3,4)
INSERT INTO T VALUES(100,1,5,6)
GO
CREATE FUNCTION f_GetFinalTime(@StartTime int)
RETURNS int
AS
BEGIN
DECLARE @FinalTime int
SELECT @FinalTime = EndTime FROM T WHERE StartTime = @StartTime
WHILE (@@ROWCOUNT <>0)
BEGIN
SELECT @FinalTime = EndTime FROM T WHERE StartTime = @FinalTime
END
RETURN @FinalTime
END
GO
SELECT Card_ID,
Locator_ID,
StartTime,
dbo.f_GetFinalTime(StartTime)
FROM T
WHERE NOT EXISTS (SELECT *
FROM T TX
WHERE TX.EndTime = T.StartTime)發糞塗牆 2014-07-28
- 打赏
- 举报
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-28 10:54:53
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Card_ID] int,[Locator_ID] int,[StartTime] int,[EndTime] int)
insert [huang]
select 100,1,1,2 union all
select 100,1,2,3 union all
select 100,1,3,4 union all
select 100,1,5,6
--------------开始查询--------------------------
;WITH cte AS (
select * ,ROW_NUMBER()OVER(PARTITION BY [Card_ID],[Locator_ID] ORDER BY [StartTime])oid
from [huang]),
cte1 AS
(
SELECT * ,1 [level]
FROM cte WHERE oid=1
UNION ALL
SELECT a.Card_ID,a.Locator_ID,CASE WHEN a.starttime=b.endtime THEN b.starttime ELSE a.starttime END starttime,CASE WHEN b.starttime=a.endtime THEN b.starttime ELSE a.endtime END endtime ,a.oid ,
CASE WHEN a.starttime=b.endtime OR b.starttime=a.endtime THEN b.[level] ELSE b.[level]+1 END [level]
FROM cte a INNER JOIN cte1 b ON a.Card_ID=b.Card_ID AND a.Locator_ID=b.Locator_ID AND a.oid=b.oid+1
)
SELECT Card_ID,Locator_ID,MIN(starttime)starttime,MAX(endtime)endtime
FROM cte1
GROUP BY Card_ID,Locator_ID,[level]
----------------结果----------------------------
/*
Card_ID Locator_ID StartTime EndTime
-----------------------------------------------------------------------
100 1 1 4
100 1 5 6
*/
唐诗三百首 2014-07-28
- 打赏
- 举报
create table T
(Card_ID int,Locator_ID int,StartTime int,EndTime int)
insert into T
select 100,1,1,2 union all
select 100,1,2,3 union all
select 100,1,3,4 union all
select 100,1,5,6
with v as
(select a.Card_ID,a.Locator_ID,a.StartTime,a.EndTime,a.StartTime 'x'
from T a
where not exists(select 1
from T b
where b.Card_ID=a.Card_ID and b.Locator_ID=a.Locator_ID
and b.EndTime=a.StartTime)
union all
select d.Card_ID,d.Locator_ID,d.StartTime,d.EndTime,c.x
from v c
inner join T d on c.Card_ID=d.Card_ID and c.Locator_ID=d.Locator_ID
and c.EndTime=d.StartTime)
select Card_ID,Locator_ID,min(StartTime) 'StartTime',max(EndTime) 'EndTime'
from v
group by Card_ID,Locator_ID,x
/*
Card_ID Locator_ID StartTime EndTime
----------- ----------- ----------- -----------
100 1 1 4
100 1 5 6
(2 row(s) affected)
*/
小白索引 2014-07-28
- 打赏
- 举报
再次感谢各位，尤其是DBA_Huangzj。
小白索引 2014-07-28
- 打赏
- 举报
试过了，但是执行太慢了。
[/quote]
看来可以出来结果哈 
[/quote]
效率的问题是我移植之后缺少条件造成的，感谢。
[/quote]
看来可以出来结果哈 [/quote]
效率的问题是我移植之后缺少条件造成的，感谢。小白索引 2014-07-28
- 打赏
- 举报
谢谢，我最终利用你提供的办法解决了问题。效率的问题是我移植之后缺少条件造成的，再次感谢。
Tiger_Zhao 2014-07-28
- 打赏
- 举报
试过了，但是执行太慢了。[/quote]
看来可以出来结果哈 [/quote]
想要结果快，就得加冗余字段
[/quote]
看来可以出来结果哈 [/quote]
想要结果快，就得加冗余字段
ALTER TABLE T
ADD FinalTime int
GO
UPDATE T
SET FinalTime = EndTime
WHERE NOT EXISTS (SELECT *
FROM T TX
WHERE T.EndTime = TX.StartTime)
WHILE (@@ROWCOUNT<>0)
BEGIN
UPDATE T
SET FinalTime = TX.FinalTime
FROM T, T TX
WHERE T.EndTime = TX.StartTime
AND T.FinalTime IS NULL
AND TX.FinalTime IS NOT NULL
END
GO
SELECT Card_ID,
Locator_ID,
StartTime,
FinalTime
FROM T
WHERE NOT EXISTS (SELECT *
FROM T TX
WHERE TX.EndTime = T.StartTime)
wanglei8 2014-07-28
- 打赏
- 举报
试过了，但是执行太慢了。[/quote]
看来可以出来结果哈 發糞塗牆 2014-07-28
- 打赏
- 举报
这就要递归。
小白索引 2014-07-28
- 打赏
- 举报
但是达不到使@cnt变量自增的目的
發糞塗牆 2014-07-28
- 打赏
- 举报
case when里面直接用@cnt+1，不用再加@cnt=@cnt+1
小白索引 2014-07-28
- 打赏
- 举报
case when Flag = 0 then ( @cnt=@cnt+1) end
小白索引 2014-07-28
- 打赏
- 举报
嗯 我那个帖子里@cnt也是这个目的，但是我不明白level为什么能够累积。
我这里只是121212这样而不是123456[/quote]这个实际上是分组，不是单纯累加，同一组的就会一样，否则才+1[/quote]
我对sql不太了解，level能够累加，是不是因为cte1这个数据集有点“递归”意思？
發糞塗牆 2014-07-28
- 打赏
- 举报
那现在还有什么问题？
加载更多回复（23）
