34,838
社区成员
发帖
与我相关
我的任务
分享SQL实现相邻行相减？有难度
表test
id,t1,t2,t3
1,10,20,30
3,9,18,20
7,8,15,16
id不是连续的数字
t1,t2,t3都是数值型
实现结果如下：
t1,t2,t3,t4,t5,t6
10,20,30,1,2,10 ---t4,t5,t6的值为第1行减去第2行
9,18,20,1,3,4 ---t4,t5,t6的值为第2行减去第3行
8,15,16,8,15,16 ---t4,t5,t6的值为第3行减去0行
解决立即给分，多谢！
id,t1,t2,t3
1,10,20,30
3,9,18,20
7,8,15,16
id不是连续的数字
t1,t2,t3都是数值型
实现结果如下：
t1,t2,t3,t4,t5,t6
10,20,30,1,2,10 ---t4,t5,t6的值为第1行减去第2行
9,18,20,1,3,4 ---t4,t5,t6的值为第2行减去第3行
8,15,16,8,15,16 ---t4,t5,t6的值为第3行减去0行
解决立即给分，多谢！
...全文
请发表友善的回复…
发表回复
-狙击手- 2009-10-27
- 打赏
- 举报
强人啊
--小F-- 2009-10-27
- 打赏
- 举报
[Quote=引用 12 楼 jycjyc 的回复:]
强人啊，都看花了，有没有人说说哪种写法效率最高呢？如果表有1000万条记录的话。
[/Quote]
都差不多
强人啊，都看花了，有没有人说说哪种写法效率最高呢？如果表有1000万条记录的话。
[/Quote]
都差不多
好记忆不如烂笔头abc 2009-10-27
- 打赏
- 举报
强人啊，都看花了，有没有人说说哪种写法效率最高呢？如果表有1000万条记录的话。
--小F-- 2009-10-27
- 打赏
- 举报
----写错了一点
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-10-27 14:58:23
-- 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)
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] varchar(3),[t1] int,[t2] int,[t3] int)
insert [test]
select '1',10,20,30 union all
select '3',9,18,20 union all
select '7',8,15,16
--------------开始查询--------------------------
select
a.t1,a.t2,a.t3,(a.t1-isnull(b.t1,0)) as t4,(a.t2-isnull(b.t2,0)) as t5,(a.t3-isnull(b.t3,0)) as t6
from
(select row_number()over(order by getdate()) ida,* from test)a
left join
(select row_number()over(order by getdate())-1 idb,* from test)b
on
a.ida = b.idb
----------------结果----------------------------
/*t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- -----------
10 20 30 1 2 10
9 18 20 1 3 4
8 15 16 8 15 16
(3 行受影响)
*/--小F-- 2009-10-27
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-10-27 14:58:23
-- 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)
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] varchar(3),[t1] int,[t2] int,[t3] int)
insert [test]
select '1',10,20,30 union all
select '3',9,18,20 union all
select '7',8,15,16
--------------开始查询--------------------------
select
a.t1,a.t2,a.t3,(a.t1-isnull(b.t1,0)) as t4,(a.t2-isnull(b.t2,0)) as t5,(b.t3-isnull(b.t3,0)) as t6
from
(select row_number()over(order by getdate()) ida,* from test)a
left join
(select row_number()over(order by getdate())-1 idb,* from test)b
on
a.ida = b.idb
----------------结果----------------------------
/*t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- -----------
10 20 30 1 2 0
9 18 20 1 3 0
8 15 16 8 15 NULL
(3 行受影响)
*/
SQL77 2009-10-27
- 打赏
- 举报
DECLARE @TEST TABLE(id INT,t1 INT ,t2 INT ,t3 INT)
INSERT @TEST
SELECT 1,10,20,30 UNION ALL
SELECT 3,9,18,20 UNION ALL
SELECT 7,8,15,16
--SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM @TEST
SELECT T.ID,T.T1,T.T2,T.T3,
ISNULL(T1.T1,T.T1)T1,
ISNULL(T1.T2,T.T2)T2,ISNULL(T1.T3,T.T3)T3
FROM #T T,
(SELECT T.IDD,T.T1-T1.T1 AS T1,T.T2-T1.T2 AS T2,T.T3-T1.T3 AS T3
FROM #T T1 RIGHT JOIN #T T ON T.IDD=T1.IDD-1)AS T1
WHERE T.IDD=T1.IDD
（所影响的行数为 3 行）
ID T1 T2 T3 T1 T2 T3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 1 2 10
3 9 18 20 1 3 4
7 8 15 16 8 15 16
（所影响的行数为 3 行）
xiequan2 2009-10-27
- 打赏
- 举报
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[t1] int,[t2] int,[t3] int)
insert [test]
select 1,10,20,30 union all
select 3,9,18,20 union all
select 7,8,15,16
select *,
t4=t1-isnull((select top 1 t1 from test where id>t.id),0),
t5=t2-isnull((select top 1 t2 from test where id>t.id ),0),
t6=t3-isnull((select top 1 t3 from test where id>t.id ),0)
from [test] t
/*
id t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 1 2 10
3 9 18 20 1 3 4
7 8 15 16 8 15 16
*/
bancxc 2009-10-27
- 打赏
- 举报
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(id int,t1 int,t2 int,t3 int)
go
insert into tb select
1,10,20,30 union all select
3,9,18,20 union all select
7,8,15,16
go
;with cte
as
(
select ROW_NUMBER() over(Order by id asc) id,t1,t2,t3 from tb
)
select
t1.t1,
t1.t2,
t1.t3,
t4=(IsNull(t1.t1,0)-IsNull(t2.t1,0)),
t5=(IsNull(t1.t2,0)-IsNull(t2.t2,0)),
t6=(IsNull(t1.t3,0)-IsNull(t2.t3,0))
from cte t1
left join
cte t2 on t1.id = t2.id -1
/*t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- -----------
10 20 30 1 2 10
9 18 20 1 3 4
8 15 16 8 15 16
(3 行受影响)
*/SQL77 2009-10-27
- 打赏
- 举报
（所影响的行数为 3 行）
ID T1 T2 T3 T1 T2 T3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 1 2 10
3 9 18 20 1 3 4
7 8 15 16 8 15 16
（所影响的行数为 3 行）
feixianxxx 2009-10-27
- 打赏
- 举报
create table test (id int,t1 int,t2 int,t3 int)
insert test select
1,10,20,30 union all select
3,9,18,20 union all select
7,8,15,16
go
select
t1,t2,t3,
t4=case when ID=(select MAX(id) from test ) then t1
else t1-(select t1 from test where p.p=id ) end ,
t5=case when ID=(select MAX(id) from test ) then t2
else t2-(select t2 from test where p.p=id ) end ,
t6=case when ID=(select MAX(id) from test ) then t3
else t3-(select t3 from test where p.p=id ) end
from
(
select *
from test a cross apply (select MIN(id) as p from test where id>a.id) p
) p
/*
t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- -----------
10 20 30 1 2 10
9 18 20 1 3 4
8 15 16 8 15 16
*/
gsk09 2009-10-27
- 打赏
- 举报
SQLServer2005
declare @test table ([id] int,[t1] int,[t2] int,[t3] int)
insert into @test
select 1,10,20,30 union all
select 3,9,18,20 union all
select 7,8,15,16
select t1.id,t1.t1,t1.t2,t1.t3
,(t1.t1-isnull(t2.t1,0)) t4
,(t1.t2-isnull(t2.t2,0)) t5
,(t1.t3-isnull(t2.t3,0)) t6
from
(select row_number()over(order by getdate()) idx,* from @test)t1
left join
(select row_number()over(order by getdate())-1 idx,* from @test)t2
on t1.idx = t2.idx
id t1 t2 t3 t4 t5 t6
---- ---- ---- --- --- --- --
1 10 20 30 1 2 10
3 9 18 20 1 3 4
7 8 15 16 8 15 16
虫洞 2009-10-27
- 打赏
- 举报
id不连续就插入一列新连续id
再当普通相邻行相减
再当普通相邻行相减
华夏小卒 2009-10-27
- 打赏
- 举报
declare @t table(id int,t1 int,t2 int,t3 int)
insert @t select
1,10,20,30 union all select
3,9,18,20 union all select
7,8,15,16
select *,id1=identity(int,1,1) into #t from @t
select a.t1,a.t2,a.t3,
t4=a.t1-isnull(b.t1,0),
t5=a.t2-isnull(b.t2,0),
t6=a.t3-isnull(b.t3,0)
from #t a
left join #t b
on a.id1=b.id1-1
t1 t2 t3 t4 t5 t6
----------- ----------- ----------- ----------- ----------- -----------
10 20 30 1 2 10
9 18 20 1 3 4
8 15 16 8 15 16
(3 行受影响)
drop table #t
ming_Y 2009-10-27
- 打赏
- 举报
等高手!
