id jg1 jg2 rq sj
1 100 110 20070503 10:00
2 120 130 20070504 10:00
3 130 100 20070505 10:00
4 null 120 20070506 1000
5 140 105 20070507 10:00我要建个存储过程,每次调用得到如下结果
名称 价格 日期 涨跌
jg1 140 20070507 如果上一个为空就和在上一个比较 +10
jg2 105 20050507 -15
1 100 110 20070503 10:00
2 120 130 20070504 10:00
3 130 100 20070505 10:00
4 null 120 20070506 1000
5 140 105 20070507 10:00我要建个存储过程,每次调用得到如下结果
名称 价格 日期 涨跌
jg1 140 20070507 如果上一个为空就和在上一个比较 +10
jg2 105 20050507 -15
drop table tcreate table t(id int,jg1 int,jg2 int,rq datetime)
insert t select 1,100,110,'2007-05-03'
union all select 2,120,130,'2007-05-04'
union all select 3,130,100,'2007-05-05'
union all select 4,null,120,'2007-05-06'
union all select 5,140,105,'2007-05-07'select * from tdeclare @id int,@jg1 int,@jg2 int
set @id = (select max(id) from t)
set @jg1 = (select jg1 from t where t.id =@id)-(select jg1 from t where t.id =@id-1)
set @jg2 = (select jg2 from t where t.id =@id)-(select jg2 from t where t.id =@id-1)
select 'jg1' as '名称',jg1 as '价格',convert(varchar(10),rq,12) as '日期',
@jg1 as '涨跌'
from t where t.id =@id
union all
select 'jg2' as '名称',jg2 as '价格',convert(varchar(10),rq,12) as '日期',
@jg2 as '涨跌'
from t where t.id =@id名称 价格 日期 涨跌
---- ----------------------
jg1 140 070507 NULL
jg2 105 070507 -15其实你的表格中最好不要用NULL,我用了case when 语句,碰到NULL就歇菜了
最好如果价格一样的话,还是插个数值比较好。
set @id = (select max(id) from t)
select @a = jg1 from t where id =@id
select @b = jg2 from t where id =@id
select top 1 @a = @a -jg1 from t where id <@id and jg1 is not null order by id desc
select top 1 @b= @b -jg2 from t where id <@id and jg2 is not null order by id desc
select 'jg1' as '名称',jg1 as '价格',convert(varchar(10),rq,12) as '日期',
@a as '涨跌'
from t where t.id =@id
union all
select 'jg2' as '名称',jg2 as '价格',convert(varchar(10),rq,12) as '日期',
@b as '涨跌'
from t where t.id =@id名称 价格 日期 涨跌
---- ----------------------
jg1 140 070507 10
jg2 105 070507 -15
我完全是写出来的
借用上面的数据
create table t(id int,jg1 int,jg2 int,rq datetime)
insert t select 1,100,110,'2007-05-03'
union all select 2,120,130,'2007-05-04'
union all select 3,130,100,'2007-05-05'
union all select 4,null,120,'2007-05-06'
union all select 5,140,105,'2007-05-07'select * from t
create proc sp_t
@rq datetimeas
begin select 名称='jg1' ,价格= (select jg1 from t where rq=@rq),
日期=(select convert(varchar(20),rq) from t where rq=@rq),
涨跌=(select jg1 from t where rq=@rq)-(select jg1 from t where
id=(select max(id) from t where id<(select id from t where rq=@rq)
and jg1 is not null ) ) union all
select 名称='jg2' ,价格= (select jg2 from t where rq=@rq),
日期=(select convert(varchar(20),rq) from t where rq=@rq),
涨跌=(select jg2 from t where rq=@rq)-(select jg2 from t where
id=(select max(id) from t where
id<(select id from t where rq=@rq)
and jg2 is not null ) )
endexec sp_t @rq='2007-05-05 00:00:00.000'日期转化我忘记了 你自己看下
jg1 140 05 7 2007 12:00AM 10
jg2 105 05 7 2007 12:00AM -15
(所影响的行数为 2 行)