SQL查询计算股票均价的一些疑问
各位SQL达人，请教有一个计算股票均价的问题，
1. 现有数据表：
classnamedate openhighlowclosevolumeamount
TDAg(T+D)5/27/20118170828579868220235355019193200640
TDAg(T+D)5/26/20118101848581018335216133817893718016
TDAg(T+D)5/25/20117810798578107970163533212943652864
TDAg(T+D)5/24/201175907735754477198802426732090880
TDAg(T+D)5/23/201176997706753075387029585350213120
TDAg(T+D)5/20/20117665774075457693141536410828949504
TDAg(T+D)5/19/20117525779774517673208348015965707264
2.想计算出的结果是：
classnamedateMA5MA10MA20MA30MA60
其中 MA5计算方式是 5天连续收盘价的AVG, 同理MA10，MA20。指的是10天，20天的收盘价移动平均。
查询MA5 T-SQL的语句是：
select b.class,b.name,b.date,avg(b.clo_2) as MA5
From (
select t1.*,t2.clo as CLO_2 from data_RN t1 inner join data_RN t2 on t1.name= t2.name and t2.rn<=t1.rn and t2.rn>=t1.rn-4
)b
group by b.class,b.name,b.rn,b.date
Order by name,date desc
3.现在的问题是，如何讲MA5到MA60一次性的计算出来呢？ 如果不用T-SQL直接计算，这种情况是不是应该使用SSIS？Thanks in advance,
[解决办法]
- SQL code
--如果上市公司名叫name,要从一个总表中得到各股票的均价,则select name,(select avg(close) from(select top 5 close from tb where name=a.name order by date desc)t) as MA5,(select avg(close) from(select top 10 close from tb where name=a.name order by date desc)t) as MA10,(select avg(close) from(select top 20 close from tb where name=a.name order by date desc)t) as MA20,(select avg(close) from(select top 30 close from tb where name=a.name order by date desc)t) as MA30,(select avg(close) from(select top 60 close from tb where name=a.name order by date desc)t) as MA60from tb a
[解决办法]