--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ASXCode varchar(3),Date datetime,ClosePrice money,PriceChange money)
insert into #T
select 'AAC','2002-06-30',.6642,null union all
select 'AAC','2003-06-30',1.0707,null union all
select 'AAC','2004-06-30',1.2591,null union all
select 'AAC','2004-12-30',1.5962,null union all
select 'AAC','2005-12-30',1.5600,null union all
select 'AAC','2006-12-30',1.8500,null union all
select 'AAC','2007-12-30',3.2000,null union all
select 'AAM','2004-06-30',.4339,null union all
select 'AAM','2005-06-30',.2358,null union all
select 'AAM','2006-06-30',.1745,null union all
select 'AAM','2007-06-30',.1950,null union all
select 'AAO','2006-06-30',.1300,null union all
select 'AAO','2007-06-30',.1850,null union all
select 'AAQ','2005-06-30',.6500,null union all
select 'AAQ','2006-06-30',.3900,null union all
select 'AAQ','2007-06-30',.6200,null union all
select 'AAS','2001-06-30',.3600,null union all
select 'AAS','2002-06-30',.7400,null union all
select 'AAS','2003-06-30',.4500,null union all
select 'AAS','2004-06-30',.4200,null union all
select 'AAS','2005-06-30',.4850,null union all
select 'AAS','2006-06-30',.1500,null union all
select 'AAS','2007-06-30',.1350,null;with T as
(
select id=row_number()over(partition by ASXCode order by Date),* from #T
)
update t set t.PriceChange=isnull((a.ClosePrice-b.ClosePrice)/b.ClosePrice,0) from #T as t inner join T as a on t.ASXCode=a.ASXCode and t.Date=a.Date left join T as b on a.ASXCode=b.ASXCode and a.id=b.id+1select * from #T/*
ASXCode Date ClosePrice PriceChange
------- ----------------------- --------------------- ---------------------
AAC 2002-06-30 00:00:00.000 0.6642 0.00
AAC 2003-06-30 00:00:00.000 1.0707 0.612
AAC 2004-06-30 00:00:00.000 1.2591 0.1759
AAC 2004-12-30 00:00:00.000 1.5962 0.2677
AAC 2005-12-30 00:00:00.000 1.56 -0.0226
AAC 2006-12-30 00:00:00.000 1.85 0.1858
AAC 2007-12-30 00:00:00.000 3.20 0.7297
AAM 2004-06-30 00:00:00.000 0.4339 0.00
AAM 2005-06-30 00:00:00.000 0.2358 -0.4565
AAM 2006-06-30 00:00:00.000 0.1745 -0.2599
AAM 2007-06-30 00:00:00.000 0.195 0.1174
AAO 2006-06-30 00:00:00.000 0.13 0.00
AAO 2007-06-30 00:00:00.000 0.185 0.423
AAQ 2005-06-30 00:00:00.000 0.65 0.00
AAQ 2006-06-30 00:00:00.000 0.39 -0.40
AAQ 2007-06-30 00:00:00.000 0.62 0.5897
AAS 2001-06-30 00:00:00.000 0.36 0.00
AAS 2002-06-30 00:00:00.000 0.74 1.0555
AAS 2003-06-30 00:00:00.000 0.45 -0.3918
AAS 2004-06-30 00:00:00.000 0.42 -0.0666
AAS 2005-06-30 00:00:00.000 0.485 0.1547
AAS 2006-06-30 00:00:00.000 0.15 -0.6907
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ASXCode varchar(3),Date datetime,ClosePrice money,PriceChange money)
insert into #T
select 'AAC','2002-06-30',.6642,null union all
select 'AAC','2003-06-30',1.0707,null union all
select 'AAC','2004-06-30',1.2591,null union all
select 'AAC','2004-12-30',1.5962,null union all
select 'AAC','2005-12-30',1.5600,null union all
select 'AAC','2006-12-30',1.8500,null union all
select 'AAC','2007-12-30',3.2000,null union all
select 'AAM','2004-06-30',.4339,null union all
select 'AAM','2005-06-30',.2358,null union all
select 'AAM','2006-06-30',.1745,null union all
select 'AAM','2007-06-30',.1950,null union all
select 'AAO','2006-06-30',.1300,null union all
select 'AAO','2007-06-30',.1850,null union all
select 'AAQ','2005-06-30',.6500,null union all
select 'AAQ','2006-06-30',.3900,null union all
select 'AAQ','2007-06-30',.6200,null union all
select 'AAS','2001-06-30',.3600,null union all
select 'AAS','2002-06-30',.7400,null union all
select 'AAS','2003-06-30',.4500,null union all
select 'AAS','2004-06-30',.4200,null union all
select 'AAS','2005-06-30',.4850,null union all
select 'AAS','2006-06-30',.1500,null union all
select 'AAS','2007-06-30',.1350,null;with T as
(
select id=row_number()over(partition by ASXCode order by Date),* from #T
)
update t set t.PriceChange=isnull((a.ClosePrice-b.ClosePrice)/b.ClosePrice,0) from #T as t inner join T as a on t.ASXCode=a.ASXCode and t.Date=a.Date left join T as b on a.ASXCode=b.ASXCode and a.id=b.id+1select * from #T/*
ASXCode Date ClosePrice PriceChange
------- ----------------------- --------------------- ---------------------
AAC 2002-06-30 00:00:00.000 0.6642 0.00
AAC 2003-06-30 00:00:00.000 1.0707 0.612
AAC 2004-06-30 00:00:00.000 1.2591 0.1759
AAC 2004-12-30 00:00:00.000 1.5962 0.2677
AAC 2005-12-30 00:00:00.000 1.56 -0.0226
AAC 2006-12-30 00:00:00.000 1.85 0.1858
AAC 2007-12-30 00:00:00.000 3.20 0.7297
AAM 2004-06-30 00:00:00.000 0.4339 0.00
AAM 2005-06-30 00:00:00.000 0.2358 -0.4565
AAM 2006-06-30 00:00:00.000 0.1745 -0.2599
AAM 2007-06-30 00:00:00.000 0.195 0.1174
AAO 2006-06-30 00:00:00.000 0.13 0.00
AAO 2007-06-30 00:00:00.000 0.185 0.423
AAQ 2005-06-30 00:00:00.000 0.65 0.00
AAQ 2006-06-30 00:00:00.000 0.39 -0.40
AAQ 2007-06-30 00:00:00.000 0.62 0.5897
AAS 2001-06-30 00:00:00.000 0.36 0.00
AAS 2002-06-30 00:00:00.000 0.74 1.0555
AAS 2003-06-30 00:00:00.000 0.45 -0.3918
AAS 2004-06-30 00:00:00.000 0.42 -0.0666
AAS 2005-06-30 00:00:00.000 0.485 0.1547
AAS 2006-06-30 00:00:00.000 0.15 -0.6907
*/
CREATE View vTestP
as
select TOP 100 PERCENT ASXCode,[Date] ,ClosePrice
from TestP
order by ASXCode,[Date]---就是不知道怎么写这个update PriceChange 语句!
请高手们解解!
看上去是2005??????
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ASXCode varchar(3),Date datetime,ClosePrice money,PriceChange money)
insert into #T
select 'AAC','2002-06-30',.6642,null union all
select 'AAC','2003-06-30',1.0707,null union all
select 'AAC','2004-06-30',1.2591,null union all
select 'AAC','2004-12-30',1.5962,null union all
select 'AAC','2005-12-30',1.5600,null union all
select 'AAC','2006-12-30',1.8500,null union all
select 'AAC','2007-12-30',3.2000,null union all
select 'AAM','2004-06-30',.4339,null union all
select 'AAM','2005-06-30',.2358,null union all
select 'AAM','2006-06-30',.1745,null union all
select 'AAM','2007-06-30',.1950,null union all
select 'AAO','2006-06-30',.1300,null union all
select 'AAO','2007-06-30',.1850,null union all
select 'AAQ','2005-06-30',.6500,null union all
select 'AAQ','2006-06-30',.3900,null union all
select 'AAQ','2007-06-30',.6200,null union all
select 'AAS','2001-06-30',.3600,null union all
select 'AAS','2002-06-30',.7400,null union all
select 'AAS','2003-06-30',.4500,null union all
select 'AAS','2004-06-30',.4200,null union all
select 'AAS','2005-06-30',.4850,null union all
select 'AAS','2006-06-30',.1500,null union all
select 'AAS','2007-06-30',.1350,null;with T as
(
select id=row_number()over(partition by ASXCode order by Date),* from #T
)
update t
set t.PriceChange=case when isnull(b.ClosePrice,0)<=0 then 0 else isnull((a.ClosePrice-b.ClosePrice)/b.ClosePrice,0) end
from #T as t
inner join T as a on t.ASXCode=a.ASXCode and t.Date=a.Date
left join T as b on a.ASXCode=b.ASXCode and a.id=b.id+1select * from #T/*
ASXCode Date ClosePrice PriceChange
------- ----------------------- --------------------- ---------------------
AAC 2002-06-30 00:00:00.000 0.6642 0.00
AAC 2003-06-30 00:00:00.000 1.0707 0.612
AAC 2004-06-30 00:00:00.000 1.2591 0.1759
AAC 2004-12-30 00:00:00.000 1.5962 0.2677
AAC 2005-12-30 00:00:00.000 1.56 -0.0226
AAC 2006-12-30 00:00:00.000 1.85 0.1858
AAC 2007-12-30 00:00:00.000 3.20 0.7297
AAM 2004-06-30 00:00:00.000 0.4339 0.00
AAM 2005-06-30 00:00:00.000 0.2358 -0.4565
AAM 2006-06-30 00:00:00.000 0.1745 -0.2599
AAM 2007-06-30 00:00:00.000 0.195 0.1174
AAO 2006-06-30 00:00:00.000 0.13 0.00
AAO 2007-06-30 00:00:00.000 0.185 0.423
AAQ 2005-06-30 00:00:00.000 0.65 0.00
AAQ 2006-06-30 00:00:00.000 0.39 -0.40
AAQ 2007-06-30 00:00:00.000 0.62 0.5897
AAS 2001-06-30 00:00:00.000 0.36 0.00
AAS 2002-06-30 00:00:00.000 0.74 1.0555
AAS 2003-06-30 00:00:00.000 0.45 -0.3918
AAS 2004-06-30 00:00:00.000 0.42 -0.0666
AAS 2005-06-30 00:00:00.000 0.485 0.1547
AAS 2006-06-30 00:00:00.000 0.15 -0.6907
*//*
-->2000
select id=identity(int,1,1) into # from #T order by ASXCode, Date
update t
set t.PriceChange=case when isnull(b.ClosePrice,0)<=0 then 0 else isnull((a.ClosePrice-b.ClosePrice)/b.ClosePrice,0) end
from #T as t
inner join # as a on t.ASXCode=a.ASXCode and t.Date=a.Date
left join # as b on a.ASXCode=b.ASXCode and a.id=b.id+1
*/
/*回复:代码'20080512004 总:00000000004 */
/*主题:求上下两行值的计算SQL语句 */
/*作者:二等草 */
/********************************************//************例子数据 begin****************/
declare @tb table(ASXCode varchar(10),Date datetime,ClosePrice dec(10,4),PriceChange dec(10,4))insert @tb select 'AAC', '2002-06-30', 0.6642,0
insert @tb select 'AAC', '2003-06-30', 1.0707,0
insert @tb select 'AAC', '2004-06-30', 1.2591,0
insert @tb select 'AAC', '2004-12-30', 1.5962,0
insert @tb select 'AAC', '2005-12-30', 1.5600,0
insert @tb select 'AAC', '2006-12-30', 1.8500,0
insert @tb select 'AAC', '2007-12-30', 3.2000,0
insert @tb select 'AAM', '2004-06-30', 0.4339,0
insert @tb select 'AAM', '2005-06-30', 0.2358,0
insert @tb select 'AAM', '2006-06-30', 0.1745,0
insert @tb select 'AAM', '2007-06-30', 0.1950,0
insert @tb select 'AAO', '2006-06-30', 0.1300,0
insert @tb select 'AAO', '2007-06-30', 0.1850,0
insert @tb select 'AAQ', '2005-06-30', 0.6500,0
insert @tb select 'AAQ', '2006-06-30', 0.3900,0
insert @tb select 'AAQ', '2007-06-30', 0.6200,0
insert @tb select 'AAS', '2001-06-30', 0.3600,0
insert @tb select 'AAS', '2002-06-30', 0.7400,0
insert @tb select 'AAS', '2003-06-30', 0.4500,0
insert @tb select 'AAS', '2004-06-30', 0.4200,0
insert @tb select 'AAS', '2005-06-30', 0.4850,0
insert @tb select 'AAS', '2006-06-30', 0.1500,0
insert @tb select 'AAS', '2007-06-30', 0.1350,0/************例子数据 end******************//************代码 begin***************/
declare @pc1 dec(10,4),@pc2 dec(10,4),@ac1 varchar(10),@ac2 varchar(10)
select @pc1 = 0,@pc2 = 0,@ac1 = '',@ac2 = ''
update @tb set PriceChange = case when @pc1<>0 then (@pc2-@pc1)/@pc1 else 0 end
,@ac1 = @ac2,@ac2 = ASXCode
,@pc1 = case when @ac1 = @ac2 then @pc2 else 0 end
,@pc2 = ClosePrice
select asxcode,closeprice,pricechange from @tb
/************代码 end*****************/asxcode closeprice pricechange
---------- ------------ ------------
AAC .6642 .0000
AAC 1.0707 .6120
AAC 1.2591 .1760
AAC 1.5962 .2677
AAC 1.5600 -.0227
AAC 1.8500 .1859
AAC 3.2000 .7297
AAM .4339 .0000
AAM .2358 -.4566
AAM .1745 -.2600
AAM .1950 .1175
AAO .1300 .0000
AAO .1850 .4231
AAQ .6500 .0000
AAQ .3900 -.4000
AAQ .6200 .5897
AAS .3600 .0000
AAS .7400 1.0556
AAS .4500 -.3919
AAS .4200 -.0667
AAS .4850 .1548
AAS .1500 -.6907
AAS .1350 -.1000/************结果 begin***************
gx
-----------------------------------------------------------
a1/a2
b1
c1/c2/c3
d1
e2
************结果 end*********************//************清除*************************/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ASXCode varchar(3),Date datetime,ClosePrice money,PriceChange money)
insert into #T
select 'AAC','2002-06-30',.6642,null union all
select 'AAC','2003-06-30',1.0707,null union all
select 'AAC','2004-06-30',1.2591,null union all
select 'AAC','2004-12-30',1.5962,null union all
select 'AAC','2005-12-30',1.5600,null union all
select 'AAC','2006-12-30',1.8500,null union all
select 'AAC','2007-12-30',3.2000,null union all
select 'AAM','2004-06-30',.4339,null union all
select 'AAM','2005-06-30',.2358,null union all
select 'AAM','2006-06-30',.1745,null union all
select 'AAM','2007-06-30',.1950,null union all
select 'AAO','2006-06-30',.1300,null union all
select 'AAO','2007-06-30',.1850,null union all
select 'AAQ','2005-06-30',.6500,null union all
select 'AAQ','2006-06-30',.3900,null union all
select 'AAQ','2007-06-30',.6200,null union all
select 'AAS','2001-06-30',.3600,null union all
select 'AAS','2002-06-30',.7400,null union all
select 'AAS','2003-06-30',.4500,null union all
select 'AAS','2004-06-30',.4200,null union all
select 'AAS','2005-06-30',.4850,null union all
select 'AAS','2006-06-30',.1500,null union all
select 'AAS','2007-06-30',.1350,null;
update a set pricechange=(case when (select top 1 closeprice from #t where a.asxcode=asxcode and date<a.date order by date desc ) is null then 0.00 else
(closeprice-(select top 1 closeprice from #t where a.asxcode=asxcode and date<a.date order by date desc ))/(select top 1 closeprice from #t where a.asxcode=asxcode and date<a.date order by date desc ) end)
from #t a