去掉间隔中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
笨办法，测试可以过，看看
------解决方案--------------------
好象只能用游标
[解决办法]
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
[解决办法]
--搞定了
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
--结果
/*
fidt
------------------
108:21:00
408:41:00
908:51:00
*/
[解决办法]
j叔叔的可以
[解决办法]
我写个傻方法吧
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
[解决办法]
--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