读书人
栏目▼

找到唯一的区间

发布时间： 2012-12-14 10:33:08 作者： rapoo

找出唯一的区间
表格如下：

id num time
a 0 9:01
a 1 9:02
a 1 9:03
a 1 9:04
a 0 9:05
a 1 9:06
a 1 9:07
a 1 9:08


b 1 9:01
b 1 9:02
b 1 9:03
b 1 9:04
b 1 9:05
b 0 9:06
b 1 9:07
b 1 9:08


c 1 9:01
c 1 9:02
c 0 9:03
c 1 9:04
c 1 9:05
c 0 9:06
c 1 9:07
c 1 9:08


d 1 9:01
d 1 9:02
d 1 9:03
d 1 9:04
d 1 9:05
d 0 9:06
d 0 9:07
d 1 9:08


e 1 9:01
e 0 9:02
e 0 9:03
e 1 9:04
e 1 9:05
e 1 9:06
e 1 9:07
e 0 9:08


.. .. ...

希望找出 某段时间内 连续是1 的最长区间，而且一定保证是唯一的一个。

比如 e 9:04 9:07 全是1,而且 没有其他id 在9:04 9:07 重合。

b 9:01 9:05 与 d 9:01 9:05 也都是1 ，但是重合，所以排除.

希望 找出 e 9:04 9:07这个区间。

2000系统，
[最优解释]
DECLARE @timestamp NVARCHAR(4)

SET @timestamp='9:07'


--create #1
IF object_id('tempdb..#1')IS NOT NULL
DROP TABLE #1
SELECT
a.id,b.num,b.time,CONVERT(INT,0) AS count_id
INTO #1
FROM t1 AS a
INNER JOIN t1 AS b ON a.id=b.id AND b.time<=@timestamp AND b.time>ISNULL((SELECT MAX(time) FROM t1 AS x WHERE x.id=b.id AND x.time<=@timestamp AND x.num=0),'')
WHERE a.time<=@timestamp
AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.id=a.id AND x.time=@timestamp AND x.num=1)
GROUP BY a.id,b.num,b.time

UPDATE a
SET count_id=(SELECT COUNT(id) FROM #1 WHERE id=a.id)
FROM #1 AS a



SELECT
a.id
,a.num
,a.time
FROM #1 AS a


WHERE NOT EXISTS(SELECT 1 FROM #1 AS x
WHERE a.id<>x.id AND count_id>a.count_id
)
AND NOT EXISTS(SELECT 1 FROM #1 AS x
WHERE a.id<>x.id AND x.count_id=a.count_id
)


[其他解释]
---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-11-05 16:08:55
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
    drop table #t
    drop table #t2
go
CREATE TABLE [tb] (id VARCHAR(1),num INT,time datetime)
INSERT INTO [tb]
SELECT 'a',0,'9:01' UNION ALL
SELECT 'a',1,'9:02' UNION ALL
SELECT 'a',1,'9:03' UNION ALL
SELECT 'a',1,'9:04' UNION ALL
SELECT 'a',0,'9:05' UNION ALL
SELECT 'a',1,'9:06' UNION ALL
SELECT 'a',1,'9:07' UNION ALL
SELECT 'a',1,'9:08' UNION ALL
SELECT 'b',1,'9:01' UNION ALL
SELECT 'b',1,'9:02' UNION ALL
SELECT 'b',1,'9:03' UNION ALL
SELECT 'b',1,'9:04' UNION ALL
SELECT 'b',1,'9:05' UNION ALL
SELECT 'b',0,'9:06' UNION ALL
SELECT 'b',1,'9:07' UNION ALL
SELECT 'b',1,'9:08' UNION ALL
SELECT 'c',1,'9:01' UNION ALL
SELECT 'c',1,'9:02' UNION ALL
SELECT 'c',0,'9:03' UNION ALL
SELECT 'c',1,'9:04' UNION ALL
SELECT 'c',1,'9:05' UNION ALL
SELECT 'c',0,'9:06' UNION ALL
SELECT 'c',1,'9:07' UNION ALL
SELECT 'c',1,'9:08' UNION ALL
SELECT 'd',1,'9:01' UNION ALL
SELECT 'd',1,'9:02' UNION ALL
SELECT 'd',1,'9:03' UNION ALL
SELECT 'd',1,'9:04' UNION ALL
SELECT 'd',1,'9:05' UNION ALL
SELECT 'd',0,'9:06' UNION ALL
SELECT 'd',0,'9:07' UNION ALL
SELECT 'd',1,'9:08' UNION ALL
SELECT 'e',1,'9:01' UNION ALL
SELECT 'e',0,'9:02' UNION ALL
SELECT 'e',0,'9:03' UNION ALL
SELECT 'e',1,'9:04' UNION ALL
SELECT 'e',1,'9:05' UNION ALL
SELECT 'e',1,'9:06' UNION ALL
SELECT 'e',1,'9:07' UNION ALL
SELECT 'e',0,'9:08'
select px=identity(int,1,1),* into #t from tb
select distinct id,num,begintime=(select min(time) from #t where num=t.num and id=t.id and time<=t.time),
         endtime=(select max(time) from #t where num=t.num and id=t.id and time>t.time and px<(select top 1 px from #t where num!=t.num and id=t.id and px>t.px))
into #t2 
from [#t] t
where num=1

select top 1 *,diff=datediff(mi,begintime,endtime) from #t2
where endtime is not null  
order by datediff(mi,begintime,endtime) desc

e11900-01-01 09:01:00.0001900-01-01 09:07:00.0006 
 

[其他解释]
哦，写的还是有些问题，54了吧
[其他解释]
高手都到哪里去了？


请出手吧！？
[其他解释]

----------------------------
-- Author  :TravyLee
-- Date    :2012-11-06 09:00:47
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--Jul  9 2008 14:43:34 
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
go 
create table [test](
[id] varchar(1),
[num] int,
[time] time
)
insert [test]
select 'a',0,'9:01' union all
select 'a',1,'9:02' union all
select 'a',1,'9:03' union all
select 'a',1,'9:04' union all
select 'a',0,'9:05' union all
select 'a',1,'9:06' union all
select 'a',1,'9:07' union all
select 'a',1,'9:08' union all
select 'b',1,'9:01' union all
select 'b',1,'9:02' union all
select 'b',1,'9:03' union all
select 'b',1,'9:04' union all
select 'b',1,'9:05' union all
select 'b',0,'9:06' union all
select 'b',1,'9:07' union all
select 'b',1,'9:08' union all
select 'c',1,'9:01' union all
select 'c',1,'9:02' union all
select 'c',0,'9:03' union all
select 'c',1,'9:04' union all
select 'c',1,'9:05' union all
select 'c',0,'9:06' union all
select 'c',1,'9:07' union all
select 'c',1,'9:08' union all
select 'd',1,'9:01' union all
select 'd',1,'9:02' union all
select 'd',1,'9:03' union all
select 'd',1,'9:04' union all
select 'd',1,'9:05' union all
select 'd',0,'9:06' union all
select 'd',0,'9:07' union all
select 'd',1,'9:08' union all
select 'e',1,'9:01' union all
select 'e',0,'9:02' union all
select 'e',0,'9:03' union all
select 'e',1,'9:04' union all
select 'e',1,'9:05' union all
select 'e',1,'9:06' union all
select 'e',1,'9:07' union all
select 'e',0,'9:08'
--------------开始查询--------------------------

select 
*
from 
(select
*
from
(select
[id],
[num],
[time]
from
test
where
[time] between '9:01' and '9:05')t
where
not exists(select 1 from (select
[id],
[num],
[time]
from
test
where
[time] between '9:01' and '9:05')a where a.id=t.id and a.num<>t.num))a
where
not exists(select 1 from (select
*
from
(select
[id],
[num],
[time] 
 
from
test
where
[time] between '9:01' and '9:05')t
where
not exists(select 1 from (select
[id],
[num],
[time]
from
test
where
[time] between '9:01' and '9:05')a where a.id=t.id and a.num<>t.num))m where a.time=m.time and a.id<>m.id)

/*
id   num         time
---- ----------- ----------------

(0 行受影响)


*/
select 
*
from 
(select
*
from
(select
[id],
[num],
[time]
from
test
where
[time] between '9:04' and '9:07')t
where
not exists(select 1 from (select
[id],
[num],
[time]
from
test
where
[time] between '9:04' and '9:07')a where a.id=t.id and a.num<>t.num))a
where
not exists(select 1 from (select
*
from
(select
[id],
[num],
[time]
from
test
where
[time] between '9:04' and '9:07')t
where
not exists(select 1 from (select
[id],
[num],
[time]
from
test
where
[time] between '9:04' and '9:07')a where a.id=t.id and a.num<>t.num))m where a.time=m.time and a.id<>m.id)
/*
id   num         time
---- ----------- ----------------
e    1           09:04:00.0000000
e    1           09:05:00.0000000
e    1           09:06:00.0000000
e    1           09:07:00.0000000

(4 行受影响)


*/


[其他解释]
4楼，

不知道 9:01-9:05 或者 9:04-9:07 这样的明确范围，

假设  可以确定一个时间点，比如 9:05 或者 9:07 这样子，另外一边不可知。
[其他解释]
顶起




高手请出手吧！？ 
[其他解释]








这嵌套的！！









[其他解释]
引用:
4楼，

不知道 9:01-9:05 或者 9:04-9:07 这样的明确范围，

假设  可以确定一个时间点，比如 9:05 或者 9:07 这样子，另外一边不可知。


你条件都不知道  那怎么筛选数据
[其他解释]
假设 已经 可以确定一个时间点，比如 9:05 或者 9:07 这样子的终点 ，

回溯 找出 连续是1 的最长区间，而且一定保证是唯一的一个
[其他解释]
引用:
假设 已经 可以确定一个时间点，比如 9:05 或者 9:07 这样子的终点 ，

回溯 找出 连续是1 的最长区间，而且一定保证是唯一的一个


哥,a 有3的阿.
------其他解决方案-------------------- 
 
是啊，有什么问题？

最长的区间是 e 9:04 9:07，没有重合的。

  b 9:01 9:05  与 d 9:01 9:05 长度为5 ，但是重合，所以排除.
[其他解释]
引用:
是啊，有什么问题？

最长的区间是 e 9:04 9:07，没有重合的。

  b 9:01 9:05  与 d 9:01 9:05 长度为5 ，但是重合，所以排除.


如果出相同id,度相同,便查1,是全部都查?
[其他解释]
假设 已经 可以确定一个时间点，比如 9:05 或者 9:07 这样子的终点 ，

回溯 找出 连续是1 的最长区间，而且一定保证是唯一的一个 



[其他解释]
粗略了.
USE test
go

--if object_id('t1') is not null 
--drop table t1
--Go
--Create table t1([id] nvarchar(1),[num] nvarchar(1),[time] datetime)
--Insert into t1
--Select N'a',N'0','9:01'
--Union all Select N'a',N'1','9:02'
--Union all Select N'a',N'1','9:03'
--Union all Select N'a',N'1','9:04'
--Union all Select N'a',N'0','9:05'
--Union all Select N'a',N'1','9:06'
--Union all Select N'a',N'1','9:07'
--Union all Select N'a',N'1','9:08'
--Union all Select N'b',N'1','9:01'
--Union all Select N'b',N'1','9:02'
--Union all Select N'b',N'1','9:03'
--Union all Select N'b',N'1','9:04'
--Union all Select N'b',N'1','9:05'
--Union all Select N'b',N'0','9:06'
--Union all Select N'b',N'1','9:07'
--Union all Select N'b',N'1','9:08'
--Union all Select N'c',N'1','9:01'
--Union all Select N'c',N'1','9:02'
--Union all Select N'c',N'0','9:03'
--Union all Select N'c',N'1','9:04'
--Union all Select N'c',N'1','9:05'
--Union all Select N'c',N'0','9:06'
--Union all Select N'c',N'1','9:07'
--Union all Select N'c',N'1','9:08'
--Union all Select N'd',N'1','9:01'
--Union all Select N'd',N'1','9:02'
--Union all Select N'd',N'1','9:03'
--Union all Select N'd',N'1','9:04'
--Union all Select N'd',N'1','9:05'
--Union all Select N'd',N'0','9:06'
--Union all Select N'd',N'0','9:07'
--Union all Select N'd',N'1','9:08'
--Union all Select N'e',N'1','9:01'
--Union all Select N'e',N'0','9:02'
--Union all Select N'e',N'0','9:03'
--Union all Select N'e',N'1','9:04'
--Union all Select N'e',N'1','9:05'
--Union all Select N'e',N'1','9:06'
--Union all Select N'e',N'1','9:07'
--Union all Select N'e',N'0','9:08'

--create #1
IF object_id('tempdb..#1')IS NOT NULL
DROP TABLE #1
SELECT
ROW_NUMBER()OVER(PARTITION BY id ORDER BY time) AS Row
,*
INTO #1
FROM t1

--create #2
IF object_id('tempdb..#2')IS NOT NULL
DROP TABLE #2
SELECT 
ROW_NUMBER()OVER(ORDER BY id ASC)AS Row
,id
,CONVERT(NVARCHAR(4000),'')AS Section_tmp 
 
,CONVERT(BIT,0)AS Flag
INTO #2 
FROM #1 GROUP BY id

DECLARE @Sql NVARCHAR(MAX)

-- update #2
WHILE EXISTS(SELECT 1 FROM #2 WHERE Flag=0)
BEGIN

SET @Sql=''


SELECT @Sql=@Sql+CASE WHEN num=1 THEN LTRIM(Row) ELSE ',' END 
FROM #1
WHERE id=(SELECT TOP 1 id FROM #2 WHERE Flag=0 ORDER BY Row)

UPDATE #2
SET Section_tmp=@Sql
,Flag=1
WHERE id=(SELECT TOP 1 id FROM #2 WHERE Flag=0 ORDER BY Row)

END

--create #3
IF object_id('tempdb..#3')IS NOT NULL
DROP TABLE #3
CREATE TABLE #3(id NVARCHAR(1),Section bigint)

WHILE EXISTS(SELECT 1 FROM #2 WHERE Flag=1)
BEGIN

SET @Sql=''

SELECT @Sql=LTRIM(id)+''','''+REPLACE(Section_tmp,',',''' UNION SELECT '''+LTRIM(id)+''',''')+''''
FROM #2
WHERE id=(SELECT TOP 1 id from #2 WHERE Flag=1 ORDER BY Row)

UPDATE #2 SET Flag=0 WHERE id=(SELECT TOP 1 id FROM #2 WHERE Flag=1 ORDER BY Row)


INSERT INTO #3
EXEC ('SELECT N'''+@Sql)  

  
END


-- Result 1: 相同id,相同度 查全部的.
SELECT a.id,a.num,a.time FROM #1 AS a,#3 AS b
WHERE a.id=b.id
AND CHARINDEX(LTRIM(a.Row),b.Section)>0
AND NOT EXISTS(SELECT 1 FROM #3 AS x
WHERE x.id=b.id AND LEN(x.Section)>LEN(b.Section)
)
GROUP BY a.id,a.num,a.time
ORDER BY a.id,a.time ASC


Go



[其他解释]
引用:
假设 已经 可以确定一个时间点，比如 9:05 或者 9:07 这样子的终点 ，

回溯 找出 连续是1 的最长区间，而且一定保证是唯一的一个


我都了,你的有出相同id,相同度.
譬如: id=a 就有 2 3 4  6 7 8 ; id=c 就有 1 2 4 5 7 8

你的意思是: 如果出相同id,相同度,查最大的那?
[其他解释]
如果你的有出 相同id,相同度的 那怎?
[其他解释]
回溯，连续的1区间

不会有你说的那种情况。
[其他解释]
 已经 可以确定一个时间点，比如 9:05 或者 9:07 这样子的终点 ，

回溯 找出 连续是1 的最长区间，而且一定保证是唯一的一个 

比如 给出 9:05这个时间点，  b 9:01 9:05  与 d 9:01 9:05 长度为5 ，但是重合，所以全部排除. 
给出 9:07 这个时间点，  最长的区间是 e 9:04 9:07，没有重合的。

所以 得出 e 9:04 9:07


[其他解释]
USE test
go
 
--if object_id('t1') is not null 
--  drop table t1
--Go
--Create table t1([id] nvarchar(1),[num] nvarchar(1),[time] datetime)
--Insert into t1 
 
--Select N'a',N'0','9:01'
--Union all Select N'a',N'1','9:02'
--Union all Select N'a',N'1','9:03'
--Union all Select N'a',N'1','9:04'
--Union all Select N'a',N'0','9:05'
--Union all Select N'a',N'1','9:06'
--Union all Select N'a',N'1','9:07'
--Union all Select N'a',N'1','9:08'
--Union all Select N'b',N'1','9:01'
--Union all Select N'b',N'1','9:02'
--Union all Select N'b',N'1','9:03'
--Union all Select N'b',N'1','9:04'
--Union all Select N'b',N'1','9:05'
--Union all Select N'b',N'0','9:06'
--Union all Select N'b',N'1','9:07'
--Union all Select N'b',N'1','9:08'
--Union all Select N'c',N'1','9:01'
--Union all Select N'c',N'1','9:02'
--Union all Select N'c',N'0','9:03'
--Union all Select N'c',N'1','9:04'
--Union all Select N'c',N'1','9:05'
--Union all Select N'c',N'0','9:06'
--Union all Select N'c',N'1','9:07'
--Union all Select N'c',N'1','9:08'
--Union all Select N'd',N'1','9:01'
--Union all Select N'd',N'1','9:02'
--Union all Select N'd',N'1','9:03'
--Union all Select N'd',N'1','9:04'
--Union all Select N'd',N'1','9:05'
--Union all Select N'd',N'0','9:06'
--Union all Select N'd',N'0','9:07'
--Union all Select N'd',N'1','9:08'
--Union all Select N'e',N'1','9:01'
--Union all Select N'e',N'0','9:02'
--Union all Select N'e',N'0','9:03'
--Union all Select N'e',N'1','9:04'
--Union all Select N'e',N'1','9:05'
--Union all Select N'e',N'1','9:06'
--Union all Select N'e',N'1','9:07'
--Union all Select N'e',N'0','9:08'
 
 
DECLARE @timestamp NVARCHAR(4)

SET @timestamp='9:07'


--create #1
IF object_id('tempdb..#1')IS NOT NULL
    DROP TABLE #1
SELECT
        a.id,b.num,b.time,CONVERT(INT,0) AS count_id
    INTO #1
    FROM t1 AS a
INNER JOIN t1 AS b ON a.id=b.id AND b.time<=@timestamp AND b.time>ISNULL((SELECT MAX(time) FROM t1 AS x WHERE x.id=b.id AND x.time<=@timestamp AND x.num=0),'')
    WHERE a.time<=@timestamp
AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.id=a.id AND x.time=@timestamp AND x.num=1)
GROUP BY a.id,b.num,b.time

UPDATE a
SET count_id=(SELECT COUNT(id) FROM #1 WHERE id=a.id)
FROM #1 AS a



SELECT * FROM #1 AS a

WHERE NOT EXISTS(SELECT 1 FROM #1 AS x
WHERE a.id<>x.id AND count_id>a.count_id
)
AND NOT EXISTS(SELECT 1 FROM #1 AS x
WHERE a.id<>x.id AND x.count_id=a.count_id
)

DECLARE @tmp NVARCHAR(50),@Result NVARCHAR(50)
 







读书人网 >SQL Server
 热点推荐 




触屏版 | 电脑版
读书人网 m.reader8.net