SQL server按时间查询数据

偷藏星光赠你 2020-12-07 11:13:57

这样子该如何去操作，有没有大佬给个思路或者例子啥的 T~T

...全文

393 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

zpingfang 2020-12-13

打赏
举报

进来学习一下

偷藏星光赠你 2020-12-10

打赏
举报

select *,datediff(n,CONVERT(smalldatetime,SUBSTRING(A.time2,1,4)+'/'+SUBSTRING(A.time2,5,2)+'/'+SUBSTRING(A.time2,7,2)+' '+SUBSTRING(A.time2,9,2)+':'+SUBSTRING(A.time2,11,2)+':'+SUBSTRING(A.time2,13,2), 120),CONVERT(smalldatetime,SUBSTRING(B.time2,1,4)+'/'+SUBSTRING(B.time2,5,2)+'/'+SUBSTRING(B.time2,7,2)+' '+SUBSTRING(B.time2,9,2)+':'+SUBSTRING(B.time2,11,2)+':'+SUBSTRING(B.time2,13,2), 120))
from test A
cross apply (select top 1 time2 from test where A.name=name and ZT='进' and time2>A.time2 order by time2) as B
where  ZT='出'

偷藏星光赠你 2020-12-08

打赏
举报

引用 7 楼 RINK_1 的回复：

select *，datediff(minute,A.time,B.time) from table A cross apply (select top 1 time from table where A.name=name and ZT='进' and time>A.time order by time) as B where ZT='出'

感谢。请教个问题，varchar字符串存储的时间格式怎么可以用datediff计算？转换了还是报错误

RINK_1 2020-12-08

打赏
举报

引用 8 楼偷藏星光赠你的回复:

[quote=引用 7 楼 RINK_1 的回复：]select *，datediff(minute,A.time,B.time) from table A cross apply (select top 1 time from table where A.name=name and ZT='进' and time>A.time order by time) as B where ZT='出'

感谢。请教个问题，varchar字符串存储的时间格式怎么可以用datediff计算？转换了还是报错误[/quote] 用类似下面的方法试试，


DECLARE @DT VARCHAR(50)

SET @DT='2020年12月8日17:28:12'

SELECT CONVERT(DATETIME,REPLACE(REPLACE(REPLACE(@DT,'年','-'),'月','-'),'日',' '))

RINK_1 2020-12-07

打赏
举报

select *，datediff(minute,A.time,B.time) from table A cross apply (select top 1 time from table where A.name=name and ZT='进' and time>A.time order by time) as B where ZT='出'

偷藏星光赠你 2020-12-07

打赏
举报

引用 5 楼锟斤拷锟斤拷的回复：

有一个比较麻烦的思路，抛砖引玉 select DATEDIFF(HH,xx1.[TIME],xx2.[TIME]) from (一模一样的子查询) xx1, (一模一样的子查询) xx2 where xx1.进出次数=xx2.进出次数 and xx1.ZT='出' and xx2.ZT='进' 子查询里查出一张表，内容是表B加上一列第几次进出的字段，可以用row_number() over(partition by ZT order by [TIME])实现这样就可以查出“第N次出”与“第N次进”之间相差了几个小时了
select xx1.*,DATEDIFF(hh,xx1.[TIME],xx2.[TIME]) from
(select *,row_number() over(partition by ZT order by [TIME]) 进出次数 from #TA) xx1,
(select *,row_number() over(partition by ZT order by [TIME]) 进出次数 from #TA) xx2
where xx1.进出次数=xx2.进出次数 and xx1.ZT='出' and xx2.ZT='进'
至于那个AB的判断，感觉可以试试这样，先用DATEPART(HH,[TIME])获取表B里的时间字段值是几点，然后再用between来判断AB对应的时间段，获取对应的进出记录，然后再进行进出时间差的计算

感谢我去研究下

锟斤拷锟斤拷 2020-12-07

打赏
举报

有一个比较麻烦的思路，抛砖引玉 select DATEDIFF(HH,xx1.[TIME],xx2.[TIME]) from (一模一样的子查询) xx1, (一模一样的子查询) xx2 where xx1.进出次数=xx2.进出次数 and xx1.ZT='出' and xx2.ZT='进' 子查询里查出一张表，内容是表B加上一列第几次进出的字段，可以用row_number() over(partition by ZT order by [TIME])实现这样就可以查出“第N次出”与“第N次进”之间相差了几个小时了


select xx1.*,DATEDIFF(hh,xx1.[TIME],xx2.[TIME]) from
(select *,row_number() over(partition by ZT order by [TIME]) 进出次数 from #TA) xx1,
(select *,row_number() over(partition by ZT order by [TIME]) 进出次数 from #TA) xx2
where xx1.进出次数=xx2.进出次数 and xx1.ZT='出' and xx2.ZT='进'

至于那个AB的判断，感觉可以试试这样，先用DATEPART(HH,[TIME])获取表B里的时间字段值是几点，然后再用between来判断AB对应的时间段，获取对应的进出记录，然后再进行进出时间差的计算

偷藏星光赠你 2020-12-07