时间差的问题

龙龙16 2008-04-17 03:45:26
停车场系统中有 kh(卡号) jqh(机器号) sj(时间)
1 1 2008-4-3 11:23:41
1 2 2008-4-3 15:23:41
1 2 2008-4-4 10:23:47
1 1 2008-4-5 10:25:47
1 2 2008-4-7 14:23:11
其中jqh=1代表进门,jqh=2代表出门
根据jqh,kh统计出进门和出门的时间间隔,如果进门或者出门没有记录的话就不要统计,如第三条记录,该怎么做?

...全文
158 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
tim_spac 2008-04-18
  • 打赏
  • 举报
 回复
上面代码的执行结果如下：
logid logon logoff minutes
1 2008-04-03 11:23:41.000 2008-04-03 15:23:41.000 240
1 2008-04-05 10:25:47.000 2008-04-07 14:23:11.000 3118
tim_spac 2008-04-18
  • 打赏
  • 举报
 回复 
set nocount on

go

/* 关于log记录配对 * log 通常包括portid / sessionid, 该id通常会被重用，但同一时刻不会重复 * 一组记录通常包含两条：先入，后出 */ 

declare @logdata table ( 

    logid int not null, -- 类似 portid / sessionid, 该id通常会被重用，但同一时刻系统中不会重复出现  

    logtype tinyint, -- 方向， 1: login|入, 0: logout|出  

    logtime datetime -- 时间点 

) 



insert @logdata 

select 1,    1           ,'2008-4-3 11:23:41' union 

select 1,    0           ,'2008-4-3 15:23:41' union 

select 1,    0           ,'2008-4-4 10:23:47' union -- 应该抛弃的记录

select 1,    1           ,'2008-4-5 10:22:47' union -- 应该抛弃的记录

select 1,    1           ,'2008-4-5 10:25:47' union 

select 1,    0           ,'2008-4-7 14:23:11'



select a.logid, logon=a.logtime, logoff=b.logtime, minutes=ceiling(datediff(second,a.logtime,b.logtime)/60.0)

from @logdata a

join @logdata b on a.logid=b.logid and a.logtype!=b.logtype and b.logtime>a.logtime

	and not exists (select 1 from @logdata b0 where b0.logid=b.logid and b0.logtype=b.logtype 

		and b0.logtime>a.logtime and b0.logtime<b.logtime)

where a.logtype=1 -- logtype=1为开始标记

and not exists (select 1 from @logdata a0

	where a0.logid=a.logid and a0.logtype=a.logtype and a0.logtime>a.logtime

	and a0.logtime < b.logtime)

go
tim_spac 2008-04-17
  • 打赏
  • 举报
 回复 
set nocount on

go

/* 关于log记录配对 * log 通常包括portid / sessionid, 该id通常会被重用，但同一时刻不会重复 * 一组记录通常包含两条：先入，后出 */ 

declare @logdata table ( 

	logid char(36) not null, -- 类似 portid / sessionid, 该id通常会被重用，但同一时刻系统中不会重复出现  

	logtype tinyint, -- 方向， 1: login|入, 0: logout|出  

	logtime datetime -- 时间点 

) 



insert @logdata 

select 1,    1           ,'2008-4-3 11:23:41' union 

select 1,    0           ,'2008-4-3 15:23:41' union 

select 1,    0           ,'2008-4-4 10:23:47' union 

select 1,    1           ,'2008-4-5 10:25:47' union 

select 1,    0           ,'2008-4-7 14:23:11'





declare @logI table (id int identity(1,1), logid char(36), logItime datetime) 

insert into @logI -- 各个有效的进入时间点 

select logid, logItime=logtime from @logdata a 

where logtype = 1 

and exists ( 

	-- 本次退出的时间  

	select 1 from @logdata c where c.logid=a.logid and c.logtype=0 and c.logtime>a.logtime ) 

and not exists ( select 1 where

	-- 本次退出的时间  

	( select min(logtime) from @logdata c where c.logid=a.logid and c.logtype=0 and c.logtime>a.logtime ) 

	>

	-- 下次进入的时间  

	( select min(logtime) from @logdata b where logid=a.logid and logtype=1 and b.logtime>a.logtime ) 

	)

order by logid, logItime 



declare @logO table (id int identity(1,1), logid char(36), logOtime datetime) 

insert into @logO -- 各个有效的退出时间点 

select logid, logOtime=logtime from @logdata a 

where logtype = 0 

and exists ( 

	-- 本次进入的时间

	select 1 from @logdata c where c.logid=a.logid and c.logtype=1 and c.logtime<a.logtime ) 

and not exists ( select 1 where

	-- 本次进入的时间  

	( select max(logtime) from @logdata c where c.logid=a.logid and c.logtype=1 and c.logtime<a.logtime ) 

	<

	-- 上次退出的时间  

	( select max(logtime) from @logdata b where logid=a.logid and logtype=0 and b.logtime<a.logtime ) 

	) 

order by logid, logOtime 



-- 配对： 

select a.logid, loginTime = logItime, logoutTime = logOtime from @logI a, @logO b where a.id=b.id

-- 

-- logid,loginTime,logoutTime

-- 1,2008-04-03 11:23:41,2008-04-03 15:23:41

-- 1,2008-04-05 10:25:47,2008-04-07 14:23:11

--
wgqqgw 2008-04-17
  • 打赏
  • 举报
 回复
我觉得6楼那个方法ok呀，而且把只出无入的记录都能处理了啦～～

刚才弄了一下，愣是没搞定只出无入的记录。
tim_spac 2008-04-17
  • 打赏
  • 举报
 回复
你可以用我那个脚本改造一下试试看，有什么错误告诉我。
龙龙16 2008-04-17
  • 打赏
  • 举报
 回复
没人帮我了.
tim_spac 2008-04-17
  • 打赏
  • 举报
 回复
参考一下我最新的帖子：<<about Time(续3)>>
恰巧涉及记录配对的问题，大家可以讨论一下。
龙龙16 2008-04-17
  • 打赏
  • 举报
 回复
我只要最早的一个时间间隔,该怎么改了,例如我只想要kh=1的人第5和第4条的时间间隔
-狙击手- 2008-04-17
  • 打赏
  • 举报
 回复
结果贴错了

declare @t table(kh int,jqh int,sj datetime)

insert @t select

             1   ,        1           ,'2008-4-3 11:23:41' union select 

             1    ,       2           ,'2008-4-3 15:23:41' union select 

             1     ,      2           ,'2008-4-4 10:23:47' union select 

             1      ,     1           ,'2008-4-5 10:25:47' union select 

             1       ,    2           ,'2008-4-7 14:23:11'



select px=identity(int,1,1), * into # from @t order by sj





select isnull(a.kh,b.kh) as kh,isnull(a.sj,'1900-01-01') sj1,isnull(b.sj,'1900-01-01') sj2,

       [sj1-sj2 ]= datediff(mi,isnull(a.sj,dateadd(mi,1,b.sj)),isnull(b.sj,dateadd(mi,-1,a.sj)))

from

(select * from # where jqh = 1) a full join

(select * from # where jqh = 2) b

on a.kh = b.kh and a.px = b.px - 1 



drop table #

/*

kh          sj1                                                    sj2                                                    sj1-sj2     

----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 

1           2008-04-03 11:23:41.000                                2008-04-03 15:23:41.000                                240

1           1900-01-01 00:00:00.000                                2008-04-04 10:23:47.000                                -1

1           2008-04-05 10:25:47.000                                2008-04-07 14:23:11.000                                3118



（所影响的行数为 3 行）



*/
-狙击手- 2008-04-17
  • 打赏
  • 举报
 回复
当无对应记录时，显示时间差为 －1

declare @t table(kh int,jqh int,sj datetime)

insert @t select

             1   ,        1           ,'2008-4-3 11:23:41' union select 

             1    ,       2           ,'2008-4-3 15:23:41' union select 

             1     ,      2           ,'2008-4-4 10:23:47' union select 

             1      ,     1           ,'2008-4-5 10:25:47' union select 

             1       ,    2           ,'2008-4-7 14:23:11'



select px=identity(int,1,1), * into # from @t order by sj





select isnull(a.kh,b.kh) as kh,isnull(a.sj,'1900-01-01') sj1,isnull(b.sj,'1900-01-01') sj2,

       [sj1-sj2 ]= datediff(mi,isnull(a.sj,dateadd(mi,1,b.sj)),isnull(b.sj,dateadd(mi,-1,a.sj)))

from

(select * from # where jqh = 1) a full join

(select * from # where jqh = 2) b

on a.kh = b.kh and a.px = b.px - 1 



drop table #

/*

kh          sj1                                                    sj2                                                    sj1-sj2     

----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 

1           2008-04-03 11:23:41.000                                2008-04-03 15:23:41.000                                240

1           2008-04-04 10:23:47.000                                2008-04-04 10:23:47.000                                0

1           2008-04-05 10:25:47.000                                2008-04-07 14:23:11.000                                3118



（所影响的行数为 3 行）

*/
龙龙16 2008-04-17
  • 打赏
  • 举报
 回复

好的,我说明白点,算出最近的一条进门和出门的时间间隔,第三条记录因为jqh=2所以就没有进门的记录，例如算出第4和第5条的时间间隔


select top 1 kh, datediff (minute,max(case when jqh=1 then end),max(case when jqh=2 then end)) 时间间隔
这样写好像不对
sweetweiwei 2008-04-17
  • 打赏
  • 举报
 回复
--try 一哈
create table a
(
kh int, jqh int,sj smalldatetime
)
insert into a
select
1 , 1 , '2008-4-3 11:23:41' union
select
1 , 2 , '2008-4-3 15:23:41' union
select
1 , 2 , '2008-4-4 10:23:47' union
select
1 , 1 , '2008-4-5 10:25:47' union
select
1 , 2 , '2008-4-7 14:23:11'
select c.kh,c.jqh,d.jqh,datediff(s, c.sj ,d.sj) as 'sj'
from (select * from a where jqh = 1) c
full join (select * from a where jqh = 2) d on convert(char(10),c.sj,120) = convert(char(10),d.sj,120)
where not(c.sj is null or d.sj is null)
kh jqh jqh sj
----------- ----------- ----------- -----------
1 1 2 14400

(1 row(s) affected)
-狙击手- 2008-04-17
  • 打赏
  • 举报
 回复 
declare @t table(kh int,jqh int,sj datetime)

insert @t select

             1   ,        1           ,'2008-4-3 11:23:41' union select 

             1    ,       2           ,'2008-4-3 15:23:41' union select 

             1     ,      2           ,'2008-4-4 10:23:47' union select 

             1      ,     1           ,'2008-4-5 10:25:47' union select 

             1       ,    2           ,'2008-4-7 14:23:11'



select px=identity(int,1,1), * into # from @t order by sj





select isnull(a.kh,b.kh) as kh,isnull(a.sj,b.sj) sj1,isnull(b.sj,'1900-01-01') sj2,

       [sj1-sj2 ]= datediff(mi,isnull(a.sj,b.sj),isnull(b.sj,b.sj))

from

(select * from # where jqh = 1) a full join

(select * from # where jqh = 2) b

on a.kh = b.kh and a.px = b.px - 1 



drop table #

/*

kh          sj1                                                    sj2                                                    sj1-sj2     

----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 

1           2008-04-03 11:23:41.000                                2008-04-03 15:23:41.000                                240

1           2008-04-04 10:23:47.000                                2008-04-04 10:23:47.000                                0

1           2008-04-05 10:25:47.000                                2008-04-07 14:23:11.000                                3118



（所影响的行数为 3 行）

*/
十一月猪 2008-04-17
  • 打赏
  • 举报
 回复
用游标处理吧 ，麻烦点
laowan688 2008-04-17
  • 打赏
  • 举报
 回复
第三条数据怎么了
laowan688 2008-04-17
  • 打赏
  • 举报
 回复 
select datediff(day, '2007-12-12','2008-12-12')
不明白你的意思
utpcb 2008-04-17
  • 打赏
  • 举报
 回复
问得不明白说明白点
zccmy22 2008-04-17
  • 打赏
  • 举报
 回复
说实话，有点乱。好像你没说太明白，这是一张卡的记录吗？一共就这三个字段吗？
我帮你顶一下吧。
sql中跨天（00:00:00）时间差问题的解决
计算2018-12-26 23:58:00至2018-12-27 00:00:00的时间差问题： SELECT SEC_TO_TIME((TIMESTAMPDIFF(second,'2018-12-26 23:58:00' ,'2018-12-27 00:00:00' ))) AS 时间差 打印结果： 另一种时间差（秒）： ...
windows/linux双系统的时间差问题。
每次从windows进来到linux后，会发现时间总是错误的，因为回家用下xp，公司用下debian，每天两次开关机就会发现这个明显的时间差问题，尤其是在web.qq.com输入的消息的时间是完全不对的，会快了8个小时甚至更多...
MySQL处理时间差问题
SELECT TIMESTAMPDIFF(YEAR,'2003-02-01','2005-05-01') AS 时间差（年）; SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') AS 时间差（月）; SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003...
el-date-picker组件8小时时间差问题（亲测有效）
el-date-picker组件8小时时间差问题（亲测有效）
deepin与win10解决时间差问题
deepin与Ubuntu不同，时间差问题解决方式不一样。deepin官网给出了解决的方法在win10运行一个注册表类型的文件，运行之后重启官网给出的方法：戳我
MS-SQL Server

34,591

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章