如何判断数据是上升还是下降

[ylqyscw]

有一组数据，
date_1, count_1
2014-12-01, 1
2014-12-02,3
2014-12-03,5
2014-12-04,3
2014-12-05,2
2014-12-06,1
2015-01-01,5
2015-01-02,4
2015-01-03,3

怎么写SQL判断出，数据是上升还是下降状态？
希望结果
date_1, count_1,status_1
2014-12-01, 1,上升
2014-12-02,3,上升
2014-12-03,5,上升
2014-12-04,3,下降
2014-12-05,2,下降
2014-12-06,1,下降
2015-01-01,5,上升
2015-01-02,4,下降
2015-01-03,3,下降

谢谢！

[吉普赛的歌]

不明白你这个上升和下降是怎么搞出来的

[唐诗三百首]

create table wh
(date_1 varchar(10),count_1 int)

insert into wh
 select '2014-12-01',1 union all
 select '2014-12-02',3 union all
 select '2014-12-03',5 union all
 select '2014-12-04',3 union all
 select '2014-12-05',2 union all
 select '2014-12-06',1 union all
 select '2015-01-01',5 union all
 select '2015-01-02',4 union all
 select '2015-01-03',3
 

with t as 
(select date_1,
        count_1,
        row_number() over(order by date_1) 'rn' 
 from wh)
select a.date_1,
       a.count_1,
       case when b.rn is null or a.count_1>b.count_1 then '上升'
            else '下降' end 'status_1'
 from t a
 left join t b on a.rn=b.rn+1

/*
 date_1     count_1     status_1
---------- ----------- --------
2014-12-01  1           上升
2014-12-02  3           上升
2014-12-03  5           上升
2014-12-04  3           下降
2014-12-05  2           下降
2014-12-06  1           下降
2015-01-01  5           上升
2015-01-02  4           下降
2015-01-03  3           下降

(9 行受影响)
 */

[yooq_csdn]

create table #table
(date_1 varchar(10),count_1 int)
 
insert into #table
 select '2014-12-01',1 union all
 select '2014-12-02',3 union all
 select '2014-12-03',5 union all
 select '2014-12-04',3 union all
 select '2014-12-05',2 union all
 select '2014-12-06',1 union all
 select '2015-01-01',5 union all
 select '2015-01-02',4 union all
 select '2015-01-03',3

select *, 
case 
	when count_1 > (select count_1 from #table where date_1= (select top 1 date_1 from #table where date_1 <a.date_1  order by 1 desc )) then '下降'
	when count_1 < (select count_1 from #table where date_1= (select top 1 date_1 from #table where date_1 <a.date_1  order by 1 desc )) then '上升'
	when count_1 = (select count_1 from #table where date_1= (select top 1 date_1 from #table where date_1 <a.date_1  order by 1 desc )) then '不变'
else '上升'
end
from  #table a 
order by date_1

[HelloWordGirl]

;WITH cet AS ( select '2014-12-01' AS DATETIME ,1 num union all select '2014-12-02',3 union all select '2014-12-03',5 union all select '2014-12-04',3 union all select '2014-12-05',2 union all select '2014-12-06',1 union all select '2015-01-01',5 union all select '2015-01-02',4 union all select '2015-01-03',3 ),cet1 AS ( SELECT c1.DATETIME,c1.num,ROW_NUMBER()OVER(order BY c1.num)cn FROM cet c1 ) SELECT c.DATETIME, c.num, CASE WHEN c1.cn IS NULL OR c.num>c1.num THEN '上升' ELSE'下降' END'action' FROM cet1 c LEFT JOIN cet1 c1 ON c.cn=c1.cn+1