求SQL Server 语句，以相同日期为一组，求相邻两行价格之差。

用SQL语句，怎么实现 ：第4行的price－第3行的price；即第3行的price－第2行的price；即第2行的price－第1行的price。以相同日期为一组，求相邻两行价格之差，每个日期得到三个数值。
以20130101为例，即3.30－3.45＝－0.15；3.45－3.45＝0；3.45－3.33＝0.1。
得到的三个数值分别为－0.05，－0.1，0.1，然后把负数替换为2，正数替换为1，0保持不变。

      dates       number     price
   20130101       1            3.33     
   20130101       2            3.45     
   20130101       3            3.45     
   20130101       4            3.30  

   20130102       1            4.33   
   20130102       2            4.45   
   20130102       3            4.35   
   20130102       4            4.3

513071394@qqcom 发表于 2014-6-7 14:16
非常感谢你提供的方法。能帮我再进一步想想怎么实现吗？
可能是我没表述清楚，以相同日期为一组，求相邻 ...

select ROW_NUMBER() over(order by dates,number) id,dates,number,price,convert(decimal(18,2),0.00) as diff,'   ' as new_number  into #temp from mytable
declare @number int,@price decimal(18,2)
set @number=(select max(id) from #temp)
while @number>1
begin
set @price=(select price from #temp where id=@number-1)
update #temp
set diff=price-@price
,new_number=convert(varchar(2),number)+'-'+convert(varchar(2),number-1)
where id=@number
set @number=@number-1
end

select * from #temp
id                   dates                                            number      price                                 diff                                    new_number
-------------------- -------------------------------------------------- ----------- --------------------------------------- ---------------------------------
1                    20130101                                           1           3.33                                    0.00                                       
2                    20130101                                           2           3.45                                    0.12                                    2-1
3                    20130101                                           3           3.45                                    0.00                                    3-2
4                    20130101                                           4           3.30                                    -0.15                                   4-3
5                    20130102                                           1           4.33                                    1.03                                    1-0
6                    20130102                                           2           4.45                                    0.12                                    2-1
7                    20130102                                           3           4.35                                    -0.10                                   3-2
8                    20130102                                           4           4.30                                    -0.05                                   4-3

select dates,new_number,case when diff<0 then 2 when diff>0 then 1 when diff=0 then 0 end as diff from #temp where number!=1
dates                                       new_number      diff
-------------------------------------------------- ---------- -----------
20130101                                           2-1        1
20130101                                           3-2        0
20130101                                           4-3        2
20130102                                           2-1        1
20130102                                           3-2        2
20130102                                           4-3        2

求高手解答。

为什么一定要在SQL当中实现呢？

nightmarehelen 发表于 2014-6-6 11:09
为什么一定要在SQL当中实现呢？

因为数据比较多，用excel实现起来很不方便

我的方法是使用 row_number() over 函数建立一个临时表
然后一个while循环更新数据就行了。
CREATE TABLE mytable(
        dates varchar(50) NOT NULL,
        number int NOT NULL,
        price decimal(16, 2) NOT NULL
)

insert into mytable values('20130101',1,3.33)
insert into mytable values('20130101',2,3.45)
insert into mytable values('20130101',3,3.45)
insert into mytable values('20130101',4,3.30)

insert into mytable values('20130102',1,4.33)
insert into mytable values('20130102',2,4.45)
insert into mytable values('20130102',3,4.35)
insert into mytable values('20130102',4,4.3)

select ROW_NUMBER() over(order by dates,number) id,dates,number,price,0.00 as diff  into #temp from mytable
declare @number int,@price decimal(16,2)
set @number=(select max(id) from #temp)
while @number>1
begin
set @price=(select price from #temp where id=@number-1)
update #temp
set diff=price-@price
where id=@number
set @number=@number-1
end

select * from #temp



id                   dates                                              number      price                                   diff
-------------------- -------------------------------------------------- ----------- --------------------------------------- ---------------------------------------
1                    20130101                                           1           3.33                                    0.00
2                    20130101                                           2           3.45                                    0.12
3                    20130101                                           3           3.45                                    0.00
4                    20130101                                           4           3.30                                    -0.15
5                    20130102                                           1           4.33                                    0.00
6                    20130102                                           2           4.45                                    0.12
7                    20130102                                           3           4.35                                    -0.10
8                    20130102                                           4           4.30                                    -0.05

(8 行受影响)

select id,dates,number,price,case when diff<0 then 2 when diff>0 then 1 when diff=0 then 0 end as diff from #temp

id                   dates                                              number      price                                   diff
-------------------- -------------------------------------------------- ----------- --------------------------------------- -----------
1                    20130101                                           1           3.33                                    0
2                    20130101                                           2           3.45                                    1
3                    20130101                                           3           3.45                                    0
4                    20130101                                           4           3.30                                    2
5                    20130102                                           1           4.33                                    0
6                    20130102                                           2           4.45                                    1
7                    20130102                                           3           4.35                                    2
8                    20130102                                           4           4.30                                    2

(8 行受影响)

kadilase571 发表于 2014-6-6 20:13
我的方法是使用 row_number() over 函数建立一个临时表
然后一个while循环更新数据就行了。
CREATE TABLE ...

非常感谢你提供的方法。能帮我再进一步想想怎么实现吗？
可能是我没表述清楚，以相同日期为一组，求相邻两行价格之差，每个日期得到三个数值。
每组都用第4行的price减去第3行的price得到价格差；第3行的price减去第2行的price得到价格差；第2行的price减去第1行的price得到价格差。
以20130101为例，即3.30－3.45＝－0.15；3.45－3.45＝0；3.45－3.33＝0.1。即：


dates          new_number     new_price
20130101       2-1                －0.15
20130101       3-2                 0
20130101       4-3                 0.1

然后把上表new_price的负数替换为2，正数替换为1，0保持不变。因此，最后我想要的结果是：

dates          new_number     new_price
20130101       2-1                2
20130101       3-2                0
20130101       4-3                1

请再帮我看看怎么实现。是不是要以dates分组，以number作为循环更新价格差？

1/先建临时表分组排序
create tmp1 as
select    dates  ,  number ,price ,  ROW_NUMBER() OVER (PARTITION BY dates ORDER BY new_number ) sortid

2/
select t1.dates , nvl(t2.price,0) - t1.price  dif
   from tmp1 t1 left outer join
   tmp2 t2
on t1.dates=t2.dates
  and  t1.sortid=t2.sortid+1

嗯 这个很实用

然后 dif 判断 正数 就为 1 ，负数为 1，这样性能应该挺快