sql中同一个表的上下两行之间的某个字段相减有关问题

2012-03-20

sql中同一个表的上下两行之间的某个字段相减问题各位，现在我遇到这样一个问题，如下描述：表A结构如下：jobgx

sql中同一个表的上下两行之间的某个字段相减问题
各位，现在我遇到这样一个问题，如下描述：
表A结构如下：

job gx_id start_time end_time
0001011-3-1 10 2010-5-10 10:00:00 2010-5-10 15:23:10
0002695-1-2 30 2010-5-10 15:30:00 2010-5-10 18:00:00
0003625-6-2 60 2010-5-11 07:20:05 2010-5-11 11:30:00
0009658-8-1 20 2010-5-12 09:20:30 2010-5-13 12:10:00

......

我想得到表A第一行的 "end_time "与第二行的 "start_time "相减的值，第二行的 "end_time "与第三行的 "start_time "相减的值，第三行的 "end_time "与第四行的 "start_time "相减的值，以此类推，请问怎样实现呢？

[解决办法]

SQL code

select   datediff(mi,b.start_time ,a.end_time)from   (select id=row_number()over(order by getdate()),* from tb)a,   (select id=row_number()over(order by getdate()),* from tb)bwhere   a.id=b.id-1
[解决办法]
SQL code------------------------------------------------ Author : htl258(Tony)-- Date   : 2010-05-10 11:37:00-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --          Jul  9 2008 14:43:34 --          Copyright (c) 1988-2008 Microsoft Corporation--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Blog   : http://blog.csdn.net/htl258------------------------------------------------> 生成测试数据表: [tb]IF OBJECT_ID('[tb]') IS NOT NULL    DROP TABLE [tb]GOCREATE TABLE [tb] ([job] [nvarchar](20),[gx_id] [int],[start_time] [datetime],[end_time] [datetime])INSERT INTO [tb]SELECT '0001011-3-1','10','2010-5-10 10:00:00','2010-5-10 15:23:10' UNION ALLSELECT '0002695-1-2','30','2010-5-10 15:30:00','2010-5-10 18:00:00' UNION ALLSELECT '0003625-6-2','60','2010-5-11 07:20:05','2010-5-11 11:30:00' UNION ALLSELECT '0009658-8-1','20','2010-5-12 09:20:30','2010-5-13 12:10:00'--SELECT * FROM [tb]--我想得到表A第一行的 "end_time "与第二行的 "start_time "相减的值，第二行的 "end_time "与第三行的 "start_time "相减的值，--第三行的 "end_time "与第四行的 "start_time "相减的值，以此类推，请问怎样实现呢？-->SQL查询如下:;with t as(    select rn=row_number()over(ORDER by job),*    from tb)select a.*,val=datediff(mi,a.end_time,b.bstart_time)from t a    outer apply(        select bstart_time=start_time         from t         where a.rn=rn-1        ) b/*rn                   job                  gx_id       start_time              end_time                val-------------------- -------------------- ----------- ----------------------- ----------------------- -----------1                    0001011-3-1          10          2010-05-10 10:00:00.000 2010-05-10 15:23:10.000 72                    0002695-1-2          30          2010-05-10 15:30:00.000 2010-05-10 18:00:00.000 8003                    0003625-6-2          60          2010-05-11 07:20:05.000 2010-05-11 11:30:00.000 13104                    0009658-8-1          20          2010-05-12 09:20:30.000 2010-05-13 12:10:00.000 NULL(4 行受影响)*/ 
 
[解决办法]
SQL code--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([job] varchar(11),[gx_id] int,[start_time] datetime,[end_time] datetime)insert [tb]select '0001011-3-1',10,'2010-5-10 10:00:00','2010-5-10 15:23:10' union allselect '0002695-1-2',30,'2010-5-10 15:30:00','2010-5-10 18:00:00' union allselect '0003625-6-2',60,'2010-5-11 07:20:05','2010-5-11 11:30:00' union allselect '0009658-8-1',20,'2010-5-12 09:20:30','2010-5-13 12:10:00'--------------------------------查询开始------------------------------select   datediff(mi,isnull(b.start_time,a.end_time) ,a.end_time)from   (select id=row_number()over(order by getdate()),* from tb)aleft join    (select id=row_number()over(order by getdate()),* from tb)bon   a.id=b.id-1/*------------7-800-13100(4 行受影响)*/

热点排行