select tid=identity(int,1,1),* into # from tbselect b.data-a.data from # a,#b where a.id=b.id-1drop table #
要是oracle的話,就用lead()函數了. sql server 的話就用1樓的,新增序號列來實現.
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-17 11:32:40 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Data] int) insert [tb] select 1 union all select 5 union all select 10 union all select 4 --------------开始查询-------------------------- select id=row_number() over(order by getdate()),* into #t from tb select b.* from #t a join #t b on a.id=b.id-1 drop table #t ----------------结果---------------------------- /* id Data -------------------- ----------- 2 5 3 10 4 4(3 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-17 11:32:40 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Data] int) insert [tb] select 1 union all select 5 union all select 10 union all select 4 --------------开始查询-------------------------- select id=row_number() over(order by getdate()),* into #t from tb select (b.data-a.data) as data from #t a join #t b on a.id=b.id-1 drop table #t ----------------结果---------------------------- /* data ----------- 4 5 -6(3 行受影响) */
select id=identity(int,1,1),* into # from tbselect b.data-a.data from # a,#b where a.id=b.id-1drop table # 1楼笔误写多了一个t
from # a,#b
where a.id=b.id-1drop table #
sql server 的話就用1樓的,新增序號列來實現.
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-17 11:32:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Data] int)
insert [tb]
select 1 union all
select 5 union all
select 10 union all
select 4
--------------开始查询--------------------------
select id=row_number() over(order by getdate()),* into #t from tb
select b.* from #t a join #t b on a.id=b.id-1
drop table #t
----------------结果----------------------------
/* id Data
-------------------- -----------
2 5
3 10
4 4(3 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-17 11:32:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Data] int)
insert [tb]
select 1 union all
select 5 union all
select 10 union all
select 4
--------------开始查询--------------------------
select id=row_number() over(order by getdate()),* into #t from tb
select (b.data-a.data) as data from #t a join #t b on a.id=b.id-1
drop table #t
----------------结果----------------------------
/*
data
-----------
4
5
-6(3 行受影响)
*/
select id=identity(int,1,1),* into # from tbselect b.data-a.data
from # a,#b
where a.id=b.id-1drop table #
1楼笔误写多了一个t