34,575
社区成员
发帖
与我相关
我的任务
分享
-------------------------------------
-- 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;
--> 测试数据: @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
--> 测试数据: @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
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
*/
-------------------------------------
-- 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;