当前位置：首页 → 问答吧 → 求一条sql语句>>

求一条sql语句>>

时间：2011-12-08

来源：互联网

SQL code

tb1
 col1    col2           Time
 A001      A      2011-12-08 09:20:46
 A001      B      2011-12-08 09:20:10
 A001      C      2011-12-08 09:19:33
 A001      D      2011-12-08 09:19:06
 A002      A      2011-12-08 09:18:32
 A002      F      2011-12-08 09:18:00
 A002      H      2011-12-08 09:17:23
 A003      M      2011-12-08 09:16:51
 A003      N      2011-12-08 09:16:21
  
tb2
 col1    col2           Time                    StTime       NetTime
 A001      A      2011-12-08 09:20:46           
 A001      B      2011-12-08 09:20:10             30            6
 A001      C      2011-12-08 09:19:33             30            13
 A001      D      2011-12-08 09:19:06             30            -3
 A002      A      2011-12-08 09:18:32             30            4
 A002      F      2011-12-08 09:18:00             30            2
 A002      H      2011-12-08 09:17:23             30            7
 A003      M      2011-12-08 09:16:51             30            -6
 A003      N      2011-12-08 09:16:21             30            0

其中 col1+col2 为主键
tb2中 NetTime 是下一条记录中(Time)加上 30秒后
再减去上一条记录(Time)得到的时间差

请问用sql语句怎么来实现．．．？

作者: guodabao 发布时间: 2011-12-08

意思有点不理解、你直接把你想要的结果贴出来好看点

作者: szstephenzhou 发布时间: 2011-12-08

tb2 就是我要得到的结果...

作者: yinliaobao 发布时间: 2011-12-08

SQL code

select 
  a.*,
  datediff(ss,a.time,dateadd(ss,30,b.time)) as nettime
from
  (select px=row_number()over(partition by col1,col2 order by getdate()),* from tb1) a
left join
  (select px=row_number()over(partition by col1,col2 order by getdate()),* from tb1) b
on
  a.col1=b.col1 and a.col2=b.col2 and a.px=b.px-1

作者: fredrickhu 发布时间: 2011-12-08

tb2中 NetTime 是下一条记录中(Time)加上 30秒后
再减去上一条记录(Time)得到的时间差

貌似你tb2 nettime的结果不对吧

作者: HEROWANG 发布时间: 2011-12-08

引用 3 楼 fredrickhu 的回复:
SQL code
select
a.*,
datediff(ss,a.time,dateadd(ss,30,b.time)) as nettime
from
(select px=row_number()over(partition by col1,col2 order by getdate()),* from tb1) a
left join
(select px……

这条sql语句好像有点问题哦
NetTime列得不到任何值。。。。

作者: yinliaobao 发布时间: 2011-12-08

你这结果有问题

作者: anlianganl 发布时间: 2011-12-08

SQL code


--楼主给出的结果好像不对
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 col1 varchar(10),
 col2 varchar(10),
 time datetime
)
go
insert into tb
select 'A001','A','2011-12-08 09:20:46' union all
select 'A001','B','2011-12-08 09:20:10' union all
select 'A001','C','2011-12-08 09:19:33' union all
select 'A001','D','2011-12-08 09:19:06' union all
select 'A002','A','2011-12-08 09:18:32' union all
select 'A002','F','2011-12-08 09:18:00' union all
select 'A002','H','2011-12-08 09:17:23' union all
select 'A003','M','2011-12-08 09:16:51' union all
select 'A003','N','2011-12-08 09:16:21'
go
with cte as(
select *,row=row_number() over(order by col1,col2) from tb
) 
select *,datediff(ss,dateadd(ss,30,time),(select top 1 time from cte where row<a.row order by row desc)) from cte a
go
/*
col1       col2       time                    row                  
---------- ---------- ----------------------- -------------------- -----------
A001       A          2011-12-08 09:20:46.000 1                    NULL
A001       B          2011-12-08 09:20:10.000 2                    6
A001       C          2011-12-08 09:19:33.000 3                    7
A001       D          2011-12-08 09:19:06.000 4                    -3
A002       A          2011-12-08 09:18:32.000 5                    4
A002       F          2011-12-08 09:18:00.000 6                    2
A002       H          2011-12-08 09:17:23.000 7                    7
A003       M          2011-12-08 09:16:51.000 8                    2
A003       N          2011-12-08 09:16:21.000 9                    0

(9 行受影响)


*/

作者: pengxuan 发布时间: 2011-12-08

求一条sql语句>>

热门阅读

热门下载