22,207
社区成员
发帖
与我相关
我的任务
分享mssql 时间对比问题 同表同字段 数据的时间对比
求 同表同字段 数据的时间对比 语句
如：
ID DATE_TIME
1 2010年3月31日 15:14:46
2 2010年3月31日 15:20:54
3 2010年3月31日 15:23:01
4 2010年3月31日 15:25:06
5 2010年3月31日 15:45:11
判断上一条和下一条的时间差
如：
ID DATE_TIME
1 2010年3月31日 15:14:46
2 2010年3月31日 15:20:54
3 2010年3月31日 15:23:01
4 2010年3月31日 15:25:06
5 2010年3月31日 15:45:11
判断上一条和下一条的时间差
...全文
请发表友善的回复…
发表回复
jwwyqs 2010-03-31
- 打赏
- 举报
id 不一定是连续的 也要处理吧
jwwyqs 2010-03-31
- 打赏
- 举报
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[DATE_TIME] DATETIME)
INSERT [tb]
SELECT 1,N'2010-3-31 15:14:46' UNION ALL
SELECT 2,N'2010-3-31 15:20:54' UNION ALL
SELECT 3,N'2010-3-31 15:23:01' UNION ALL
SELECT 6,N'2010-3-31 15:25:06' UNION ALL
SELECT 8,N'2010-3-31 15:45:11'
select a.id, abs(datediff(second,a.DATE_TIME,b.DATE_TIME)) '时间差'
from
(select ROW_NUMBER() OVER (ORDER BY id ASC) AS newid,* from tb
) a
left join (select ROW_NUMBER() OVER (ORDER BY id ASC) AS newid,* from tb
) b on b.newid=a.newid+1
/*
1 368
2 127
3 125
6 1205
8 NULL
*/
ws_hgo 2010-03-31
- 打赏
- 举报
[Quote=引用 13 楼 neumik 的回复:]
引用 7 楼 leo_lesley 的回复:
SQL code
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,……
[/Quote]
看下我8，10楼的
8楼的以分钟
10楼的是秒钟
引用 7 楼 leo_lesley 的回复:
SQL code
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,……
[/Quote]
看下我8，10楼的
8楼的以分钟
10楼的是秒钟
清风道禅 2010-03-31
- 打赏
- 举报
[Quote=引用 7 楼 leo_lesley 的回复:]
SQL code
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,(select top 1 date_time fro……
[/Quote]
您的好多负数啊···为什么··
-13148640
···
SQL code
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,(select top 1 date_time fro……
[/Quote]
您的好多负数啊···为什么··
-13148640
···
htl258_Tony 2010-03-31
- 打赏
- 举报
[Quote=引用 3 楼 htl258 的回复:]
SQL code
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-03-31 15:27:35
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.25……
[/Quote]
如果要精确到秒,把我代码中的 datediff(mi ... 改为 datediff(ss ... 即可
SQL code
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-03-31 15:27:35
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.25……
[/Quote]
如果要精确到秒,把我代码中的 datediff(mi ... 改为 datediff(ss ... 即可
ws_hgo 2010-03-31
- 打赏
- 举报
LZ人了
出来说个话
出来说个话
ws_hgo 2010-03-31
- 打赏
- 举报
8楼写的是分钟
如果是秒的话
create table #TT
(
ID int identity(1,1) primary key,
DATE_TIME datetime
)
insert into #TT select '2010-3-31 15:14:46'
insert into #TT select '2010-3-31 15:20:54'
insert into #TT select '2010-3-31 15:23:01'
insert into #TT select '2010-3-31 15:25:06'
insert into #TT select '2010-3-31 15:45:11'
--1
select abs(datediff(second,T1,T2)) '时间差'
from
(
select DATE_TIME T1,
isnull((select DATE_TIME from #TT where ID=T.ID-1),DATE_TIME) T2
from #TT T
) tt
时间差
-----------
0
368
127
125
1205
(5 行受影响)
--2
select abs(datediff(second,T1,T2)) '时间差'
from
(
select DATE_TIME T1,
isnull((select DATE_TIME from #TT where ID=T.ID-1),DATE_TIME) T2
from #TT T
) tt where abs(datediff(mi,T1,T2))>0
时间差
-----------
368
127
125
1205
(4 行受影响)
leo_lesley 2010-03-31
- 打赏
- 举报
create table tab (ID int ,DATE_TIME datetime)
go
insert tab
select 1 ,'2010-3-31 15:14:46'
union select 2 ,'2010-3-31 15:20:54'
union select 3 ,'2010-3-31 15:23:01'
union select 4 ,'2010-3-31 15:25:06'
union select 5 ,'2010-3-31 15:45:11'
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,(select top 1 date_time from tab where id>t.id order by id)),0)
from tab t
drop table tabws_hgo 2010-03-31
- 打赏
- 举报
create table #TT
(
ID int identity(1,1) primary key,
DATE_TIME datetime
)
insert into #TT select '2010-3-31 15:14:46'
insert into #TT select '2010-3-31 15:20:54'
insert into #TT select '2010-3-31 15:23:01'
insert into #TT select '2010-3-31 15:25:06'
insert into #TT select '2010-3-31 15:45:11'
--1
select abs(datediff(mi,T1,T2)) '时间差'
from
(
select DATE_TIME T1,
isnull((select DATE_TIME from #TT where ID=T.ID-1),DATE_TIME) T2
from #TT T
) tt
时间差
-----------
0
6
3
2
20
(5 行受影响)
--2
select abs(datediff(mi,T1,T2)) '时间差'
from
(
select DATE_TIME T1,
isnull((select DATE_TIME from #TT where ID=T.ID-1),DATE_TIME) T2
from #TT T
) tt where abs(datediff(mi,T1,T2))>0
时间差
-----------
6
3
2
20leo_lesley 2010-03-31
- 打赏
- 举报
select id,date_time,
[上一条]=isnull(datediff(mi,(select top 1 date_time from tab where id<t.id order by id desc),date_time),0) ,
[下一条]=isnull(datediff(mi,date_time,(select top 1 date_time from tab where id>t.id order by id)),0)
from tab t
--小F-- 2010-03-31
- 打赏
- 举报
单位是秒
ws_hgo 2010-03-31
- 打赏
- 举报
create table #TT
(
ID int identity(1,1) primary key,
DATE_TIME datetime
)
insert into #TT select '2010-3-31 15:14:46'
insert into #TT select '2010-3-31 15:20:54'
insert into #TT select '2010-3-31 15:23:01'
insert into #TT select '2010-3-31 15:25:06'
insert into #TT select '2010-3-31 15:45:11'
select abs(datediff(mi,T1,T2)) '时间差'
from
(
select DATE_TIME T1,
isnull((select DATE_TIME from #TT where ID=T.ID-1),DATE_TIME) T2
from #TT T
) tt where abs(datediff(mi,T1,T2))>0
时间差
-----------
6
3
2
20
(4 行受影响)--小F-- 2010-03-31
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2010-03-31 15:29:41
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[DATE_TIME] varchar(21))
insert [tb]
select 1,'2010年3月31日15:14:46' union all
select 2,'2010年3月31日15:20:54' union all
select 3,'2010年3月31日15:23:01' union all
select 4,'2010年3月31日15:25:06' union all
select 5,'2010年3月31日15:45:11'
--------------开始查询--------------------------
select
datediff(ss,a.time,b.time) as 时间差
from
(
select id0=row_number()over(order by getdate()),cast(replace(replace(replace([DATE_TIME],'年','-'),'月','-'),'日',' ') as datetime) as [time] from tb
)a,
(
select id0=row_number()over(order by getdate()),cast(replace(replace(replace([DATE_TIME],'年','-'),'月','-'),'日',' ') as datetime) as [time] from tb
)b
where
a.id0=b.id0-1
----------------结果----------------------------
/* 时间差
-----------
368
127
125
1205
(4 行受影响)
*/htl258_Tony 2010-03-31
- 打赏
- 举报
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-03-31 15:27:35
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[DATE_TIME] DATETIME)
INSERT [tb]
SELECT 1,N'2010-3-31 15:14:46' UNION ALL
SELECT 2,N'2010-3-31 15:20:54' UNION ALL
SELECT 3,N'2010-3-31 15:23:01' UNION ALL
SELECT 6,N'2010-3-31 15:25:06' UNION ALL
SELECT 8,N'2010-3-31 15:45:11'
GO
--SELECT * FROM [tb]
-->SQL2000查询如下:
select a.DATE_TIME a,isnull(b.DATE_TIME,a.DATE_TIME) b,
DATEDIFF(mi,a.DATE_TIME,isnull(b.DATE_TIME,a.DATE_TIME)) difftime
from (select rn=(select COUNT(1) from tb where ID<=t.id),* from tb t) a
left join (select rn=(select COUNT(1) from tb where ID<=t.id),* from tb t) b
on a.rn=b.rn-1
/*
--以分钟计
a b difftime
----------------------- ----------------------- -----------
2010-03-31 15:14:46.000 2010-03-31 15:20:54.000 6
2010-03-31 15:20:54.000 2010-03-31 15:23:01.000 3
2010-03-31 15:23:01.000 2010-03-31 15:25:06.000 2
2010-03-31 15:25:06.000 2010-03-31 15:45:11.000 20
2010-03-31 15:45:11.000 2010-03-31 15:45:11.000 0
(5 行受影响)
*/htl258_Tony 2010-03-31
- 打赏
- 举报
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-03-31 15:27:35
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[DATE_TIME] DATETIME)
INSERT [tb]
SELECT 1,N'2010-3-31 15:14:46' UNION ALL
SELECT 2,N'2010-3-31 15:20:54' UNION ALL
SELECT 3,N'2010-3-31 15:23:01' UNION ALL
SELECT 6,N'2010-3-31 15:25:06' UNION ALL
SELECT 8,N'2010-3-31 15:45:11'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;with t as
(
select rn=ROW_NUMBER()over(order by id),*
from tb
)
select a.DATE_TIME a,isnull(b.DATE_TIME,a.DATE_TIME) b,
DATEDIFF(mi,a.DATE_TIME,isnull(b.DATE_TIME,a.DATE_TIME)) difftime
from t a
left join t b
on a.rn=b.rn-1
/*
--以分钟计
a b difftime
----------------------- ----------------------- -----------
2010-03-31 15:14:46.000 2010-03-31 15:20:54.000 6
2010-03-31 15:20:54.000 2010-03-31 15:23:01.000 3
2010-03-31 15:23:01.000 2010-03-31 15:25:06.000 2
2010-03-31 15:25:06.000 2010-03-31 15:45:11.000 20
2010-03-31 15:45:11.000 2010-03-31 15:45:11.000 0
(5 行受影响)
*/--小F-- 2010-03-31
- 打赏
- 举报
这个格式...
