一道有意思的SQL 求解

[ZXUN_XUE]

表1：
ID QTY
1 100
2 80
3 90
4 120
5 100

查询出相邻ID之间的差异:(ID大的QTY-ID小的QTY)
ID QTY 差异
1 100 NULL
2 80 -20
3 90 10
4 120 30
5 100 -20

[pigwen521]

MYSQL的，测试通过。。

select 
ID,QTY,(select b1.QTY-b2.QTY from TB as b2 where b2.ID<b1.ID order by ID desc limit 1) ChaYi
from TB as b1 order by ID

[layo]

好像没什么意思啊

[WT滔滔江水]

没有一点意思！

[wallhitpig]

9楼（改进的2楼方法）和3楼的都行啊

[woolk]

select id, qty, lag(qty) over(order by rowid) - qty from t_test

[ken2su]

顶一下二楼的

[wolf553986067]

能写出来的人就觉得没意思 写不出的人就觉得有意思了

[CZP98168]

if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (ID int,QTY int)
insert into #A
select 1,100 union all
select 7,80 union all
select 3,90 union all
select 8,120 union all
select 5,100


select
  list.ID,list.QTY,case when list.LastID is null then null else list.QTY - #A.QTY end as 差异
from (
  select a.*,(select max(b.ID) from #A b where b.ID < a.ID) as LastID
  from #A a
  )list
  left join #A
    on #A.ID = list.LastID
order by list.ID

/*
ID          QTY         差异          
----------- ----------- ----------- 
1           100         NULL
3           90          -10
5           100         10
7           80          -20
8           120         40
*/

[deity0618]

好像是有点意思。

[madStone_l]

呵呵。 飘过

[sunnylai2010]

wanglingzhong的正解!

[情殇无限]

with a as(
select row_Number() over(order by id) rn,* from #a)
select x1.id,x1.qty,x1.qty-x2.qty
from a x1 left join a x2
on x1.rn=x2.rn+1

这样如何？

[superpander]

11楼正解

[SQLCenter]

确实很有意思

[aaaaabbbccd]

我是来看看的。

[andy_liucj]

if object_id('tb') is not null drop table tb
create table tb(id int, qty int)

insert tb
select 1,100 union all
select 2,80 union all
select 3,90 union all
select 4,120 union all
select 7,100


with t1 as
(
	select *, row_number() over(order by id) as sn from tb t
)

select a.id, a.qty, a.qty-b.qty gap from t1 a left join t1 b on a.sn=b.sn+1

[laurencesj]

SELECT  t.id, t.qty - t2.qty AS qtyDelta
FROM    TTTT AS t
        LEFT OUTER JOIN TTTT AS t2 ON t.id = ( SELECT  MIN(id)
                                                FROM    TTTT
                                                WHERE   id > t2.id
                                              )

[small4854]

select a.id,a.qty-b.qty
from(select ROW_NUMBER() OVER(ORDER BY id) xh,* from tb) a
left join (select ROW_NUMBER() OVER(ORDER BY id) xh,* from tb) b on a.xh=b.xh+1

[fulinkster]

select t.*,t.qty-d.qty from (select t1.id,max(t2.id) as nid from testdb t1 left join testdb t2 on t2.id<t1.id group by t1.id) r join testdb t on r.id=t.id left join testdb d on r.nid=d.id

应该没错吧

[yilvchaxiang]

嘿嘿 二楼的真是经典 28楼的更是绝了