去掉间隔中15分钟之内的时间

leohuang 2007-01-17 05:03:03

declare @t table
(fid int identity(1,1),t varchar(8))

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:54:00'

select * from @t

数据是上边的,我想得到下面的结果

1 08:21:00
4 08:51:00

也就是去掉间隔在15分钟之内的时间,咋整啊,请高手赐教,多谢
...全文
664 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
jxf654 2007-01-18
  • 打赏
  • 举报
回复
up
xk2y 2007-01-18
  • 打赏
  • 举报
回复
哦...
fightback 2007-01-18
  • 打赏
  • 举报
回复
楼上有错误

这个应该是对的,测试数据完善些

declare @t table
(fid int identity(1,1),t varchar(8))

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:41:00' union all
select '08:42:00' union all
select '08:43:00' union all
select '08:44:00' union all
select '08:45:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:59:00' union all
select '09:00:00'
--declare @qq int
--select top 1 fid from @t where fid >3 order by fid asc

--select min(fid)
--from @t (select fid from @t where fid >3 order by fid asc)


DECLARE @TIME1 DATETIME
DECLARE @min INT,@id int,@C INT
DECLARE @TIME2 DATETIME
loop_:

DECLARE t_CURSOR CURSOR FOR
SELECT t,FID
FROM @t order by fid asc
OPEN t_CURSOR
FETCH NEXT FROM t_CURSOR
INTO @TIME1,@id
WHILE @@FETCH_STATUS=0
BEGIN
print @TIME1
SELECT @TIME2 = t
FROM @t WHERE FID in (select top 1 fid from @t where fid >@id order by fid asc)
IF @@ROWCOUNT>0
BEGIN
print @TIME2+@@ROWCOUNT
print @id
select @C = datediff(minute,@TIME1,@TIME2)
--if datediff(minute,@TIME2,@TIME1) <15
print @C
if @C <15
begin
--print @C
--if datediff(minute,@TIME2,@TIME1)<15
declare @min_ int
--select @min = min(fid) from (select fid from @t where fid >@id)
--DELETE FROM @t WHERE fid = @id+1

DELETE FROM @t WHERE fid in (select top 1 fid from @t where fid >@id order by fid asc)
--declare @qq int
--select @qq = select top 1 fid from @t where fid >@id order by fid asc
--print '-----------------------'
select * from @t
CLOSE t_CURSOR
deallocate t_CURSOR
goto loop_
end
END
FETCH NEXT FROM t_CURSOR
INTO @TIME1,@id
END
CLOSE t_CURSOR
deallocate t_CURSOR
select * from @t
fightback 2007-01-18
  • 打赏
  • 举报
回复
declare @t table
(fid int identity(1,1),t varchar(8))

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:41:00' union all
select '08:42:00' union all
select '08:43:00' union all
select '08:44:00' union all
select '08:45:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:54:00'


DECLARE @TIME1 DATETIME
DECLARE @min INT,@id int,@C INT
DECLARE @TIME2 DATETIME
DECLARE t_CURSOR CURSOR FOR
SELECT t,FID
FROM @t order by fid asc
OPEN t_CURSOR
FETCH NEXT FROM t_CURSOR
INTO @TIME1,@id
WHILE @@FETCH_STATUS=0
BEGIN
print @TIME1
SELECT @TIME2 = t
FROM @t WHERE FID=@id+1
IF @@ROWCOUNT>0
BEGIN
print @TIME2+@@ROWCOUNT
print @id
select @C = datediff(minute,@TIME1,@TIME2)
--if datediff(minute,@TIME2,@TIME1) <15
if @C <15
begin
-- print @C
--if datediff(minute,@TIME2,@TIME1)<15
DELETE FROM @t WHERE fid = @id+1

-- select * from @t
end
END
FETCH NEXT FROM t_CURSOR
INTO @TIME1,@id
END
CLOSE t_CURSOR
deallocate t_CURSOR
select * from @t

笨办法,测试可以过,看看
CutBug 2007-01-18
  • 打赏
  • 举报
回复
so good!
leohuang 2007-01-18
  • 打赏
  • 举报
回复
强!

谢谢大家关注!

结贴
dssw 2007-01-18
  • 打赏
  • 举报
回复
--try
declare @t table
(fid int identity(1,1),t varchar(8))

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:41:00' union all
select '08:42:00' union all
select '08:43:00' union all
select '08:44:00' union all
select '08:45:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:57:00' union all
select '08:56:00'

--select * from @t
--select min(fid) as fid,min(t) as t ,datediff(minute,convert(varchar(8),'08:21:00',108),convert(varchar(8),t,108))%15 from @t
--group by datediff(minute,convert(varchar(8),'08:21:00',108),convert(varchar(8),t,108))%15
select min (fid),t from @t where t in
(
select min(t ) from @t
union
select distinct t1
from
(
select
(
select isnull( min(t),'*') from @t where datediff(minute,convert(varchar(8),a.t,108),convert(varchar(8),t,108))>15
) t1
from @t a
) bb
where bb.t1 !='*'
)
group by t
CutBug 2007-01-18
  • 打赏
  • 举报
回复
我写个傻方法吧
declare @d1 varchar(20)
declare @ROWS int
declare @tt table
(fid int identity(1,1),t varchar(8))

set @d1 = (select top 1 t from @t)
select @ROWS = max(fid) from @t
declare @i int
set @i = 1

while(@i<=@ROWS)
begin
set @d1 = (select top 1 isnull(t,'*') from @t where datediff(minute,cast(@d1 as datetime),cast(t as datetime))>=15)
if(@d1<>'*')
begin
insert into @tt(t) values(@d1)
end
set @i = @i+1

end
select * from @tt
marco08 2007-01-18
  • 打赏
  • 举报
回复
j叔叔的可以
CutBug 2007-01-18
  • 打赏
  • 举报
回复
可惜和要求不合啊
CutBug 2007-01-18
  • 打赏
  • 举报
回复
看贴
__________________________________
leohuang(LEO) ( ) 信誉:98 Blog
这两个
08:21:00
08:41:00

首先,第一个时间肯定要,然后往下找,找到一个比他大15分钟的就要,找到之后再找比刚找到的这个时间大15分钟的才要

依此类推

对,就是楼上说的
---------------------------------------
j9988 2007-01-18
  • 打赏
  • 举报
回复
gc_ding(施主,给个妞泡好么)方法好! 速度快.
gc_ding 2007-01-18
  • 打赏
  • 举报
回复
--搞定了
declare @t table
(fid int identity(1,1),t varchar(8))

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:41:00' union all
select '08:42:00' union all
select '08:43:00' union all
select '08:44:00' union all
select '08:45:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:54:00'

select min(fid) as fid,min(t) as t from @t
group by datediff(minute,convert(varchar(8),'08:21:00',108),convert(varchar(8),t,108))/15

--结果
/*
fid t
------------------
1 08:21:00
4 08:41:00
9 08:51:00
*/
j9988 2007-01-18
  • 打赏
  • 举报
回复
declare @t table
(fid int identity(1,1),t varchar(8),f int)--表结构改一下,加上f

insert into @t
select '08:21:00' union all
select '08:22:00' union all
select '08:23:00' union all
select '08:51:00' union all
select '08:52:00' union all
select '08:54:00'



declare @dt datetime

update @t set @dt=(case when @dt is null or datediff(minute,@dt,t)>15 then t else @dt end),
f=(case when @dt=t then 1 else 0 end)

select * from @t
/* f为1的就是,前题条件是:时间t一定要从小到大排列.
fid t f
----------- -------- -----------
1 08:21:00 1
2 08:22:00 0
3 08:23:00 0
4 08:51:00 1
5 08:52:00 0
6 08:54:00 0
*/
select * from @t where f=1
十一月猪 2007-01-18
  • 打赏
  • 举报
回复
好象只能用游标
leohuang 2007-01-18
  • 打赏
  • 举报
回复
这样整就太麻烦了

感谢楼上,还有好办法吗?
gc_ding 2007-01-17
  • 打赏
  • 举报
回复
try:
select * from @t A
where exists(select * from @t where fid>A.fid and datediff(min, t, a.t)>15)
leohuang 2007-01-17
  • 打赏
  • 举报
回复
这两个
08:21:00
08:41:00

首先,第一个时间肯定要,然后往下找,找到一个比他大15分钟的就要,找到之后再找比刚找到的这个时间大15分钟的才要

依此类推

对,就是楼上说的
rookie_one 2007-01-17
  • 打赏
  • 举报
回复
会不会是这样:?

'08:21:00' 向后推15分钟,'08:36:00' ,所以这两个期间的都不要,只保留起始点'08:21:00'
然后下个计算从'08:36:00'开始依次类推

先下班了
rookie_one 2007-01-17
  • 打赏
  • 举报
回复
影子说的是啊,
lz所说的间隔时间,需要参考点的吧,要不无法确认

加载更多回复(4)

34,575

社区成员

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

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