100分求一sql

gxjwqm 2009-11-28 03:39:11
数据:
id happentime recoverTime
1 1 3
1 2 4
1 3 5
1 6 10
2 1 5
2 3 7
2 8 9


期望结果
id happentime recoverTime
1 1 5
1 6 10
2 1 7
2 8 9

根据id 把 happentime 到recoverTime 在时间轴上画出来,有重叠的连续记录,比如 1-->3,2-->4 2在1到3之间,

所以记录为1-->4 ,又3-->5 3在 1,4之间,所以记录为1-->5 ,没有重叠的单独记录 如 6-->10

为了方便把时间换成了数字
...全文
144 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
gxjwqm 2009-11-28
  • 打赏
  • 举报
回复
消化消化
liangCK 2009-11-28
  • 打赏
  • 举报
回复
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-28 15:42:12
-------------------------------------

--> 生成测试数据: @tb
DECLARE @tb TABLE (id int,happentime int,recoverTime int)
INSERT INTO @tb
SELECT 1,1,3 UNION ALL
SELECT 1,2,4 UNION ALL
SELECT 1,3,5 UNION ALL

SELECT 1,6,10 UNION ALL
SELECT 1,8,12 UNION ALL
SELECT 1,11,15 UNION ALL

SELECT 1,16,19 UNION ALL
SELECT 1,17,50 UNION ALL

SELECT 2,1,5 UNION ALL
SELECT 2,3,7 UNION ALL

SELECT 2,8,9

--SQL查询如下:


SELECT *,flag=CASE WHEN EXISTS(SELECT * FROM @tb
WHERE A.happentime BETWEEN happentime AND recoverTime
AND A.happentime > happentime
AND id = A.id)
THEN 0 ELSE 1 END
INTO #Tmp
FROM @tb AS A

SELECT id,MIN(happentime) AS happentime,MAX(recoverTime) AS recoverTime
FROM (
SELECT *,flag2=(SELECT SUM(flag) FROM #Tmp
WHERE happentime<=A.happentime
AND id = A.id)
FROM #Tmp AS A
) AS T
GROUP BY id,flag2
ORDER BY 1,2,3;

DROP TABLE #Tmp;
pt1314917 2009-11-28
  • 打赏
  • 举报
回复
--> 测试数据: @s
declare @s table (id int,happentime int,recoverTime int)
insert into @s
select 1,1,3 union all
select 1,2,4 union all
select 1,3,5 union all
select 1,6,10 union all
select 2,1,5 union all
select 2,3,7 union all
select 2,8,9

select id,happentime,recoverTime=max(recoverTime) from
(select id,happentime=(select isnull(min(happentime),a.happentime) from @s where recovertime>=a.happentime and id=a.id and happentime<a.happentime),
recoverTime=(select isnull(max(recoverTime),a.recovertime) from @s where recovertime<=a.happentime and id=a.id and happentime>a.happentime)
from @s a)a
group by id,happentime
order by id

--结果:
id happentime recoverTime
----------- ----------- -----------
1 1 5
1 6 10
2 1 7
2 8 9
pt1314917 2009-11-28
  • 打赏
  • 举报
回复
--> 测试数据: @s
declare @s table (id int,happentime int,recoverTime int)
insert into @s
select 1,1,3 union all
select 1,2,4 union all
select 1,3,5 union all
select 1,6,10 union all
select 2,1,5 union all
select 2,3,7 union all
select 2,8,9

select id,ha,max(ha1) from
(select id,ha=(select isnull(min(happentime),a.happentime) from @s where recovertime>=a.happentime and id=a.id and happentime<a.happentime),
ha1=(select isnull(max(recoverTime),a.recovertime) from @s where recovertime<=a.happentime and id=a.id and happentime>a.happentime)
from @s a)a
group by id,ha
order by id
fcuandy 2009-11-28
  • 打赏
  • 举报
回复
DECLARE @tb TABLE (id int,happentime int,recoverTime int,v INT)
INSERT INTO @tb(id,happentime,recoverTime)
SELECT 1,1,3 UNION ALL
SELECT 1,2,4 UNION ALL
SELECT 1,3,5 UNION ALL
SELECT 1,6,10 UNION ALL
SELECT 2,1,5 UNION ALL
SELECT 2,3,7 UNION ALL
SELECT 2,8,9


DECLARE @a INT,@b INT,@id INT
UPDATE @tb
SET @a=CASE WHEN happenTime>CASE WHEN id=@id THEN @b ELSE 0 END AND id=ISNULL(@id,id) THEN happenTIme ELSE ISNULL(@a,happenTime) END,
@b=recoverTime,
@id=id,
v=@a

SELECT id,MIN(happenTIme),max(recoverTime) FROM @tb GROUP BY id,v

/*
1 1 5
1 6 10
2 1 7
2 8 9
*/
icelovey 2009-11-28
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 dawugui 的回复:]
貌似很复杂,帮顶.
[/Quote]
没看懂题目~~
dawugui 2009-11-28
  • 打赏
  • 举报
回复
貌似很复杂,帮顶.
liangCK 2009-11-28
  • 打赏
  • 举报
回复
搞错了.
liangCK 2009-11-28
  • 打赏
  • 举报
回复
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-28 15:42:12
-------------------------------------

--> 生成测试数据: @tb
DECLARE @tb TABLE (id int,happentime int,recoverTime int)
INSERT INTO @tb
SELECT 1,1,3 UNION ALL
SELECT 1,2,4 UNION ALL
SELECT 1,3,5 UNION ALL
SELECT 1,6,10 UNION ALL
SELECT 2,1,5 UNION ALL
SELECT 2,3,7 UNION ALL
SELECT 2,8,9

--SQL查询如下:

SELECT id,MIN(happentime) AS happentime,MAX(recoverTime) AS recoverTime
FROM (
SELECT *,flag=CASE WHEN EXISTS(SELECT * FROM @tb
WHERE happentime BETWEEN A.happentime AND A.recoverTime
AND recoverTime < A.recoverTime)
THEN 0 ELSE 1 END
FROM @tb AS A
) AS T
GROUP BY id,flag
ORDER BY 1,2,3;

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧