select [时间间隔(秒)]=datediff(second,a.time,b.time) from table a inner join table b on a.id=b.id-1
select datediff(second,a.time,b.time) from table1 a,table1 b where a.id=b.id-1
declare @t table(a varchar(10)) --插入数据 insert into @t select '15:30' union all select '15:32' union all select '15:33' union all select '15:36' select a.a,isnull(datediff(n,a.b,b.b),0) b from ( select rn=(select count(1) from @t where cast(replace(a,':',':') as datetime)<=cast(replace(t.a,':',':') as datetime)),*,cast(replace(a,':',':') as datetime) b from @t t ) a left join ( select rn=(select count(1) from @t where cast(replace(a,':',':') as datetime)<=cast(replace(t.a,':',':') as datetime)),*,cast(replace(a,':',':') as datetime) b from @t t ) b on a.rn=b.rn-1 /* a b ---------- ----------- 15:30 2 15:32 1 15:33 3 15:36 0(4 行受影响) */
declare @table table(id int identity(1,1),mtime datetime) insert into @table(mtime) select '2009-05-06 15:30:00' union all select '2009-05-06 15:32:00' union all select '2009-05-06 15:33:00' union all select '2009-05-06 15:36:00'select * from @tableselect a.mtime,datediff(n,a.mtime,b.mtime) as 间隔 from @table a,@table b where a.id=b.id-1 ps:暂时没有环境,没有测试,楼主试一试吧
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([time] datetime) insert [tb] select '15:30' union all select '15:32' union all select '15:33' union all select '15:36'
---查询--- select left(convert(varchar(8),a.[time],108),5) as [time], datediff(mi,a.[time],b.[time]) as 差值 from (select *,px=(select count(1)+1 from tb where [time]<t.[time]) from tb t) a left join (select *,px=(select count(1)+1 from tb where [time]<t.[time]) from tb t) b on a.px=b.px-1 ---结果--- time 差值 -------- ----------- 15:30 2 15:32 1 15:33 3 15:36 NULL(所影响的行数为 4 行)
DECLARE @tab TABLE (date DATETIME)INSERT INTO @tab SELECT '1900-01-01 '+'15:30' UNION ALL SELECT '1900-01-01 '+'15:32' UNION ALL SELECT '1900-01-01 '+'15:33' select CONVERT(DATETIME, a.date,120),[时间间隔(秒)]= DATEDIFF(minute,a.date,b.date) from ( select date, row_number() over(order by date) Orderid from @tab ) A left join ( select date, row_number() over(order by date) -1 Orderid from @tab ) B on a.Orderid = B.Orderid/* date 时间间隔(秒) ----------------------- ----------- 1900-01-01 15:30:00.000 2 1900-01-01 15:32:00.000 1 1900-01-01 15:33:00.000 NULL */
DECLARE @tab TABLE (date DATETIME)INSERT INTO @tab SELECT '15:30' UNION ALL SELECT '15:32' UNION ALL SELECT '15:33' select CONVERT(VARCHAR(8), a.date,108) date,[时间间隔(秒)]= DATEDIFF(minute ,a.date,b.date) from ( select date, row_number() over(order by date) Orderid from @tab ) A left join ( select date, row_number() over(order by date) -1 Orderid from @tab ) B on a.Orderid = B.Orderid/* date 时间间隔(秒) -------- ----------- 15:30:00 2 15:32:00 1 15:33:00 NULL */
DECLARE @tab TABLE (date DATETIME)INSERT INTO @tab SELECT '15:30' UNION ALL SELECT '15:32' UNION ALL SELECT '15:33' select CONVERT(VARCHAR(8), a.date,108) date,[时间间隔(秒)]= DATEDIFF(minute ,a.date,b.date) from ( select date, row_number() over(order by date) Orderid from @tab ) A left join ( select date, row_number() over(order by date) -1 Orderid from @tab ) B on a.Orderid = B.Orderid/* date 时间间隔(秒) -------- ----------- 15:30:00 2 15:32:00 1 15:33:00 NULL */
select [时间间隔(秒)]=datediff(second,a.time,b.time)
from table a inner join table b
on a.id=b.id-1
select datediff(second,a.time,b.time)
from table1 a,table1 b where a.id=b.id-1
--插入数据
insert into @t
select '15:30' union all
select '15:32' union all
select '15:33' union all
select '15:36' select a.a,isnull(datediff(n,a.b,b.b),0) b
from
(
select rn=(select count(1) from @t where cast(replace(a,':',':') as datetime)<=cast(replace(t.a,':',':') as datetime)),*,cast(replace(a,':',':') as datetime) b from @t t
) a left join
(
select rn=(select count(1) from @t where cast(replace(a,':',':') as datetime)<=cast(replace(t.a,':',':') as datetime)),*,cast(replace(a,':',':') as datetime) b from @t t
) b on a.rn=b.rn-1
/*
a b
---------- -----------
15:30 2
15:32 1
15:33 3
15:36 0(4 行受影响)
*/
declare @table table(id int identity(1,1),mtime datetime)
insert into @table(mtime)
select '2009-05-06 15:30:00' union all
select '2009-05-06 15:32:00' union all
select '2009-05-06 15:33:00' union all
select '2009-05-06 15:36:00'select * from @tableselect a.mtime,datediff(n,a.mtime,b.mtime) as 间隔
from @table a,@table b where a.id=b.id-1
ps:暂时没有环境,没有测试,楼主试一试吧
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([time] datetime)
insert [tb]
select '15:30' union all
select '15:32' union all
select '15:33' union all
select '15:36'
---查询---
select
left(convert(varchar(8),a.[time],108),5) as [time],
datediff(mi,a.[time],b.[time]) as 差值
from
(select *,px=(select count(1)+1 from tb where [time]<t.[time]) from tb t) a
left join
(select *,px=(select count(1)+1 from tb where [time]<t.[time]) from tb t) b
on
a.px=b.px-1
---结果---
time 差值
-------- -----------
15:30 2
15:32 1
15:33 3
15:36 NULL(所影响的行数为 4 行)
(date DATETIME)INSERT INTO @tab SELECT '1900-01-01 '+'15:30'
UNION ALL SELECT '1900-01-01 '+'15:32'
UNION ALL SELECT '1900-01-01 '+'15:33'
select CONVERT(DATETIME, a.date,120),[时间间隔(秒)]= DATEDIFF(minute,a.date,b.date)
from
(
select date, row_number() over(order by date) Orderid
from @tab ) A
left join (
select date, row_number() over(order by date) -1 Orderid
from @tab ) B
on a.Orderid = B.Orderid/*
date 时间间隔(秒)
----------------------- -----------
1900-01-01 15:30:00.000 2
1900-01-01 15:32:00.000 1
1900-01-01 15:33:00.000 NULL
*/
(date DATETIME)INSERT INTO @tab SELECT '15:30'
UNION ALL SELECT '15:32'
UNION ALL SELECT '15:33'
select CONVERT(VARCHAR(8), a.date,108) date,[时间间隔(秒)]= DATEDIFF(minute
,a.date,b.date)
from
(
select date, row_number() over(order by date) Orderid
from @tab ) A
left join (
select date, row_number() over(order by date) -1 Orderid
from @tab ) B
on a.Orderid = B.Orderid/*
date 时间间隔(秒)
-------- -----------
15:30:00 2
15:32:00 1
15:33:00 NULL
*/
(date DATETIME)INSERT INTO @tab SELECT '15:30'
UNION ALL SELECT '15:32'
UNION ALL SELECT '15:33'
select CONVERT(VARCHAR(8), a.date,108) date,[时间间隔(秒)]= DATEDIFF(minute
,a.date,b.date)
from
(
select date, row_number() over(order by date) Orderid
from @tab ) A
left join (
select date, row_number() over(order by date) -1 Orderid
from @tab ) B
on a.Orderid = B.Orderid/*
date 时间间隔(秒)
-------- -----------
15:30:00 2
15:32:00 1
15:33:00 NULL
*/