mssql 时间对比问题同表同字段数据的时间对比

2010-03-31 03:26:09 193 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

逆向工程师视角：拆解DataDome滑块验证码的3层防护机制与绕过思路本文从逆向工程师视角，深入剖析DataDome滑块验证码的三层核心防护机制。第一层通过动态加密参数实现会话绑定与抗重放；第二层采集TLS指纹、鼠标轨迹动力学等多维行为数据进行一致性验证；第三层则是基于机器学习的后端异步风险决策引擎。文章不仅拆解了其技术原理，更分享了模拟、钩取等超越单纯工具调用的底层分析与绕过思路。阅读详情

求同表同字段数据的时间对比语句

如：

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

判断上一条和下一条的时间差

...全文

VoIP概述 VoIP统一通信与VoIPVoIP与传统电话比较VoIP各项产品设备的类型及应用统一通信与VoIP 统一通信指的是把计算机与传统通信技术统一成一体的新通信模式,融合计算机网络与传统通信网络在一个网络平台上,实现电话、传真、数据传输、音视频会议、呼叫中心、即时通信等众多应用服务 VoIP又称IP电话或IP网络电话,是Voice Over IP的缩写,这种技术通过对语音信号进行编码数字化,压缩处理成压缩帧,然后转换成为IP数据包在TCP/IP网络上进行传输,从而达到了在IP网络上进行语音通信目的 VoIP与传阅读详情

清风道禅

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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楼的是秒钟

清风道禅 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
···

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 ... 即可

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 tab

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'

--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

20

leo_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

打赏
举报

这个格式...

mssql 时间对比问题 同表同字段 数据的时间对比

相关推荐

mssql 时间对比问题同表同字段数据的时间对比