股票数据查询系列问题1

Question

明：周末突发奇想，搞个非实时的日线分析股票的小东西
目前构建了两个表：
表 stockcode－－存放股票基本资料
ID stockCode stockName 
2225 000001  深发展A
2226 000002  万科Ａ
2227 000003  PT金田Ａ
2228 000004  ST国农
2229 000005  ST星源
2230 000006  深振业Ａ
2231 000007  ST达声
.......表stockprice---存放价格(日期 开盘　最低 　最高　收盘 成交量　资金)
        ID     stockCode stockDate      OPrice LPrice  HPrice  CPrice      Vol   Funds
88 000001 2007-08-31 37.65 36.95 38.1 38 173834 65528.8
89 000001 2007-08-30 37.28 36.58 38.08 37.7 246827 91801.16
90 000001 2007-08-29 37.48 36.6 37.68 36.84 267208 98924.54
91 000001 2007-08-28 38.52 37.5 38.8 37.99 313335 118790.97
92 000001 2007-08-27 39.58 38.41 40.86 39.2 275096 109384.56
93 000001 2007-08-24 38.34 38.2 40.02 39.6 389456 153840.25
94 000001 2007-08-23 38.4 37.7 38.8 38.39 264577 100479.57
95 000001 2007-08-22 37.85 37.63 39.58 38.59 315341 122444.39
96 000001 2007-08-21 38.45 37.7 39.26 38.56 335990 129941.61
97 000001 2007-08-20 36 36 38.15 37.9 366385 137115.49
98 000001 2007-08-17 35.99 34.38 36.9 34.88 420685 149258.8
99 000001 2007-08-16 38.02 35.77 38.05 36.25 385348 141712.76
100 000001 2007-08-15 38.4 37.45 38.98 38 246045 93615.79
101 000001 2007-08-14 39.2 36.99 39.33 38.35 349273 132011.89
102 000001 2007-08-13 38.5 38 40 39.1 260558 101333.41
103 000001 2007-08-10 39.8 38.48 40.1 39 185781 72887.16
104 000001 2007-08-09 38.82 38.53 40.1 40.01 308030 122424.2
105 000001 2007-08-08 38.3 38 40.5 38.82 261425 102744.42
106 000001 2007-08-07 38.95 38.27 39.45 39.1 156322 60829.17
107 000001 2007-08-06 38.99 38.26 39.89 39.18 258177 100531.83
108 000001 2007-08-03 38 37.88 39.55 39.11 438271 170625.75
109 000001 2007-08-02 35.3 35.3 37.85 37.7 396583 145256.61
110 000001 2007-08-01 36.25 34.41 37.1 35.01 291433 104275.65
111 000001 2007-07-31 33.85 33.65 36.4 36.23 418203 147946.58
112 000001 2007-07-30 33.7 33.6 35.08 34.2 244882 84494.16
113 000001 2007-07-27 34.75 33.5 34.85 33.85 337391 114857.79
114 000001 2007-07-26 35 34.75 35.5 34.99 267510 93909.2
115 000001 2007-07-25 33.36 33.36 35.38 35.2 348526 120782.08
116 000001 2007-07-24 32.98 32.5 34.65 33.36 349242 117778.08
117 000001 2007-07-23 33.4 31.76 33.4 32.95 528016 172864.06
118 000001 2007-07-19 29.9 29.69 31.29 30.48 303028 93252.64
119 000001 2007-07-18 29 28.63 31.2 30.24 563889 170325.63
120 000001 2007-07-17 27.4 27.2 29.18 29.15 329571 93194.61
121 000001 2007-07-16 28.2 26.53 28.2 26.55 176135 47827.18
122 000001 2007-07-13 29 28.03 29 28.35 75288 21298.45
123 000001 2007-07-12 28.25 28.25 29.17 28.75 152057 43750.27
124 000001 2007-07-11 28.4 28.01 28.66 28.12 114247 32276.17
125 000001 2007-07-10 28 28 29.35 28.45 335470 96413.51
126 000001 2007-07-09 27.3 27 28.5 28.1 272595 76071.62
127 000001 2007-07-06 25.5 25.41 27.12 27.01 440714 116687.33
128 000001 2007-07-05 25.7 25.14 26.87 25.2 371587 97198.99
129 000001 2007-07-04 27.45 26.1 27.55 26.2 243960 65271.05
130 000001 2007-07-03 27.55 26.73 28.15 27.23 252532 69364.51
131 000001 2007-07-02 26.81 26.57 27.86 27.4 323761 87561.47
132 000002 2007-08-31 33.58 33.21 34.3 33.8 754160 253335.47
133 000002 2007-08-30 34.02 32.95 34.8 33.58 792306 265950.36
134 000002 2007-08-29 35.8 33.88 36.89 34 1303931 458531.53
135 000002 2007-08-23 34.46 34.15 35.58 34.79 1330811 465167.15
136 000002 2007-08-22 33.3 33.3 35.8 34 919716 316670
137 000002 2007-08-21 33.4 32.73 33.75 32.93 835819 277357.15
138 000002 2007-08-20 31.88 31.88 33.28 33.07 800844 262081.94
139 000002 2007-08-17 31.75 30.7 32.58 30.85 964731 303718.93
140 000002 2007-08-16 32.8 31.9 33.58 32.08 1089108 352849.76
141 000002 2007-08-15 33.66 33.15 34.5 33.72 948210 321646.23
142 000002 2007-08-14 32.99 31.9 33.66 33.3 1074049 351751.65
143 000002 2007-08-13 32.98 32.4 34.01 33.13 872838 288910.41
144 000002 2007-08-10 35 33.09 35 33.76 1346284 456189.79
145 000002 2007-08-09 32.6 32.06 35.48 35.34 1496209 499703.04
146 000002 2007-08-08 32.85 31.61 33.09 32.27 1070375 344771.71
147 000002 2007-08-07 33.89 32.3 33.89 33.38 1097746 363393.45
148 000002 2007-08-06 34 32.9 34.4 33.97 1110633 373180.56
149 000002 2007-08-03 31.81 31.68 34.7 33.8 1325159 439760.33
150 000002 2007-08-02 29.1 29.1 31.57 31.57 936991 281905.35
151 000002 2007-08-01 28.3 27.81 29.9 28.7 1023759 296752.99
152 000002 2007-07-31 28.4 28 28.7 28.3 722511 204871.2
153 000002 2007-07-30 26.86 26.8 29.55 28.49 983216 280251.08
154 000002 2007-07-27 27 26.6 27.48 27.3 706594 190950.61
155 000002 2007-07-26 27.36 26.5 27.98 27.41 853793 231195.59
156 000002 2007-07-25 27.35 26.4 27.4 27.25 815972 219887.46
157 000002 2007-07-24 26.57 26 27.85 27.05 912813 246848.43
158 000002 2007-07-23 26.2 25.82 27.58 26.36 1332819 354001.2
159 000002 2007-07-20 23.1 23.01 25.64 25.64 1214986 303793.43
160 000002 2007-07-19 23 22.87 23.87 23.31 548016 128114.84
161 000002 2007-07-18 23.13 22.98 24.5 23.47 1313205 309351.27
162 000002 2007-07-17 20.54 20.4 22.62 22.62 979416 214194.95
163 000002 2007-07-16 21.3 20.48 21.73 20.56 463943 98214.15
164 000002 2007-07-12 21.2 20.9 21.78 21.26 547049 117001.77
165 000002 2007-07-11 20.65 20.65 21.74 21.2 984220 210237.35
166 000002 2007-07-10 20.18 20.15 21.1 20.52 1599870 331180.74
167 000002 2007-07-09 19.42 19.42 20.2 19.93 735802 146673.48
168 000002 2007-07-06 18.5 18.11 19.5 19.38 508725 96023.92
169 000002 2007-07-05 19 18.5 19.45 18.56 422597 79640
170 000002 2007-07-04 19.45 19 20.3 19.32 677896 133945.35
171 000002 2007-07-03 18.76 18.76 19.5 19.41 526655 101104.81
172 000002 2007-07-02 19 18.02 19.19 18.64 570264 106378.98初看简单，搞起来发现有好多难点，看来又是一次学习sql语法好机会
问题1:要查找出10天内涨幅<20%的股票，(注：当天涨幅=最后一个交易日收盘价/前一个交易日-1)问题2:求某股收盘价的5日均线MA，2007-08-31的均线应该是包括2007-08-31在内的最近5个交易日的收盘均价
望各位高手不吝指教

Answer 1

问题一:
--表 stockcode存放股票基本资料
create table #stockcode(ID int,stockCode varchar(10),stockName varchar(50))
insert into #stockcode
select 2225,'000001','深发展A' union all
select 2226,'000002','万科Ａ' union all
select 2227,'000003','PT金田Ａ' union all
select 2228,'000004','ST国农' union all
select 2229,'000005','ST星源' union all
select 2230,'000006','深振业Ａ' union all
select 2231,'000007','ST达声'select * from #stockcode--表stockprice存放价格(日期 开盘　最低 　最高　收盘 成交量　资金)
create table #stockprice(ID int,stockCode varchar(10),stockDate datetime,OPrice float,LPrice float,HPrice float,CPrice float,Vol float,Funds float)
insert into #stockprice
select 88,'000001','2007-08-31',37.65,36.95,38.13,38.13,81738,3465528.8 union all
select 89,'000001','2007-08-30',36.65,35.95,37.13,37.13,71738,2465528.8 union all
select 90,'000001','2007-08-29',35.65,34.95,36.13,36.13,61738,1465528.8 union all
select 91,'000001','2007-08-28',34.65,33.95,35.13,35.13,51738,3265528.8 union all
select 92,'000001','2007-08-27',33.65,32.95,34.13,34.13,41738,3365528.8 union all
select 93,'000001','2007-08-26',32.65,31.95,33.13,33.13,31738,3465528.8 union all
select 94,'000001','2007-08-25',31.65,30.95,32.13,32.13,21738,3565528.8 union all
select 95,'000001','2007-08-24',30.65,29.95,31.13,31.13,11738,3665528.8 select
a.id,a.stockCode,[当天涨幅]=a.CPrice/b.CPrice-1,tDate=a.stockDate,yDate=b.stockDate
into #T1
from #stockprice a 
left join #stockprice b
on b.stockDate=a.stockDate-1
select distinct b.tdate,maxtdate=max(a.tdate),a.stockCode,[10天涨幅]=sum(a.[当天涨幅])
into #T2
from #T1 a 
left join #T1 b
on a.tDate>b.tDate and a.tDate<=b.tDate+10 
where a.[当天涨幅]<0.2
group by b.tdate,a.stockCodeselect mintdate=min(a.tdate),b.maxtdate ,a.stockCode
into #T3
from #T2 a 
left join #T2 b
on a.tdate<b.tdate
where cast((b.tdate-a.tdate) as int)<10
group by b.maxtdate,a.stockCodeselect [计算区间]=convert(varchar(10),a.mintdate,120)+'~'+convert(varchar(10),a.maxtdate,120),b.*
from #T3 a
left join #stockcode b
on a.stockCode=b.stockCodedrop table #stockprice,#T1,#T2,#T3