表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=最大的时间
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=最大的时间
解决方案 »
- WAMP环境下如何安装sqlsrv?
- T-SQL对字符串的操作
- 如何使数据库只保留3个月数据,其他数据都在订阅数据库里?
- SQL2005
- 请问这个设计如何实现?
- 求助一个SQL语句的写法.
- 请大家帮我写一个有难度的SQL,谢谢!
- 查找引起SQL SERVER死锁操作方法,希望大家探讨
- 我在SQL Enterprise Manager中修改了一表名即表A改为表B,在VB中调用的SQL语句随即做了修改,但执行该语句时提示Invalid Object Name A,
- sql server 2000 订阅功能 不能连续复制 只能在刚刚配置好的时候复制一次 数据不能实时更新
- 数据库空值问题
- (sql) 字符 转 datetime
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:290 00:00:05 06-18 10:43:19 0 00:00:25
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'周', 1goselect 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
) c2 1 周 2007-06-18 10:43:34.000 2007-06-18 10:43:29.000 5 2007-06-18 10:43:19.000 15
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
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
晚上细看下