日期:2014-05-18  浏览次数:20791 次

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

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分钟之内的时间,咋整啊,请高手赐教,多谢

------解决方案--------------------
select * from @t A where not exists(select * from @t where t> A.t)
------解决方案--------------------
如果数据是这样的
select '08:21:00 ' union all
select '08:22:00 ' union all
select '08:23:00 ' union all
select '08:24:00 ' union all
select '08:25:00 ' union all
select '08:26:00 ' union all
select '08:27:00 ' union all
select '08:31:00 ' union all
select '08:32:00 ' union all
select '08:33:00 ' union all
select '08:34:00 ' union all
select '08:35: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 '
你要的结果会是什么
------解决方案--------------------
会不会是这样:?

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

先下班了

------解决方案--------------------
up
------解决方案--------------------
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

笨办法,测试可以过,看看
------解决方案--------------------