求个sql语句

chowcool 2007-06-24 07:23:05

表A
Num ID Time
2 1 2007-06-18 10:43:34

表B
Num ID Time Flag Leave Out
2 1 2007-06-18 10:43:29.000 0 0 3
1 1 2007-06-18 10:43:24.000 0 1 3
1 1 2007-06-18 10:43:19.000 0 1 3
1 1 2007-06-18 10:43:14.000 0 1 2
2 1 2007-06-18 10:43:09.000 1 1 2
3 1 2007-06-18 10:43:04.000 1 1 2
3 1 2007-06-18 10:42:59.000 1 1 2
2 1 2007-06-18 10:42:54.000 1 1 2
2 1 2007-06-18 10:42:49.000 1 1 2
1 1 2007-06-18 10:42:44.000 1 1 2
1 1 2007-06-18 10:42:39.000 1 1 1
3 1 2007-06-18 10:42:34.000 1 1 1
2 1 2007-06-18 10:42:29.000 1 1 1
1 1 2007-06-18 10:42:24.000 1 1 1
表C
Name ID
周 1
查询结果
Num ID Name 当前在Num时间到达Num时间在Num停留时间进入时间进入停留的时间
2 1 周 06-18 10:43:34 06-18 10:43:29 0 00:00:05 06-18 10:43:19 0 00:00:25
注释:
到达Num的时间为第一个 Leave=0 的时间
进入时间为Num=1，Out=最大的时间

...全文

323 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

sp4 2007-06-25

打赏
举报

格式有些混乱，呵呵

y_dong119 2007-06-25

打赏
举报

100分，要砸死人说！！！！！！！！
呵呵

ojuju10 2007-06-25

打赏
举报

请楼主说清楚点！

being21 2007-06-25

打赏
举报

晕死
楼主把数据整理一下吧，招人回答问题最好把自己能提供的条件提供好。
你偷懒，别人要费时间的！

Trance 2007-06-25

打赏
举报

这是什么东西啊，看不懂。。。乱。。

chowcool 2007-06-25

打赏
举报

thx lwl0606(寒泉)
晚上细看下

lwl0606 2007-06-25

打赏
举报

select num,id,name,当前在Num时间,到达Num时间,dbo.ss(datediff(s,到达Num时间,当前在Num时间)) as 在Num停留时间,进入时间,dbo.ss(datediff(s,进入时间,当前在Num时间)) as 进入停留的时间
from
(
select tabc.name,tabc.id,taba.num,taba.time as 当前在Num时间, (select top 1 time from tabb where leave=0 and id=tabc.id) as 到达Num时间,
(select top 1 time from tabb a where num=1 and id=tabc.id and not exists (select 1 from tabb b where num=1 and id=tabc.id and a.out<b.out)) as 进入时间
from tabc left join taba on tabc.id=taba.id
) c
--result
2 1 周 2007-06-18 10:43:34.000 2007-06-18 10:43:29.000 00:00:05 2007-06-18 10:43:19.000 00:00:15

lwl0606 2007-06-25

打赏
举报

还要写一个函数把 5 转换成 00:00:05 形式
CREATE FUNCTION [dbo].[ss] (@a int)
RETURNS nvarchar(50) AS
BEGIN
declare @h nvarchar(10)
declare @m nvarchar(10)
declare @s nvarchar(10)
select @h='00'+Convert(nvarchar(2),@a/3600),@m='00'+Convert(nvarchar(2),@a/60),@s='00'+Convert(nvarchar(2),@a%60)
return right(@h,2)+':'+right(@m,2)+':'+ right(@s,2)
END

lwl0606 2007-06-25

打赏
举报

create table tabA(
Num int , ID int , Time datetime)
insert into tabA select 2, 1, '2007-06-18 10:43:34'
go
create table tabB(
Num int , ID int, [Time] datetime, Flag int , Leave int , [Out] int )
insert into tabB
select
2, 1, '2007-06-18 10:43:29.000', 0, 0, 3
union select 1, 1, '2007-06-18 10:43:24.000', 0, 1, 3
union select 1, 1, '2007-06-18 10:43:19.000', 0, 1, 3
union select 1, 1, '2007-06-18 10:43:14.000', 0, 1, 2
union select 2, 1, '2007-06-18 10:43:09.000', 1, 1, 2
union select 3, 1, '2007-06-18 10:43:04.000', 1, 1, 2
union select 3, 1, '2007-06-18 10:42:59.000', 1, 1, 2
union select 2, 1, '2007-06-18 10:42:54.000', 1, 1, 2
union select 2, 1, '2007-06-18 10:42:49.000', 1, 1, 2
union select 1, 1, '2007-06-18 10:42:44.000', 1, 1, 2
union select 1, 1, '2007-06-18 10:42:39.000', 1, 1, 1
union select 3, 1, '2007-06-18 10:42:34.000', 1, 1 ,1
union select 2, 1, '2007-06-18 10:42:29.000', 1, 1, 1
union select 1, 1, '2007-06-18 10:42:24.000', 1 ,1, 1
go
create table tabC(
Name nvarchar(10), ID int )
insert into tabc
select
N'周', 1

go

select num,id,name,当前在Num时间,到达Num时间,datediff(s,到达Num时间,当前在Num时间) as 在Num停留时间,进入时间,datediff(s,进入时间,当前在Num时间) as 进入停留的时间
from
(
select tabc.name,tabc.id,taba.num,taba.time as 当前在Num时间, (select top 1 time from tabb where leave=0 and id=tabc.id) as 到达Num时间,
(select top 1 time from tabb a where num=1 and id=tabc.id and not exists (select 1 from tabb b where num=1 and id=tabc.id and a.out<b.out)) as 进入时间
from tabc left join taba on tabc.id=taba.id
) c

2 1 周 2007-06-18 10:43:34.000 2007-06-18 10:43:29.000 5 2007-06-18 10:43:19.000 15

chowcool 2007-06-25