股票数据查询系列问题1
明：周末突发奇想，搞个非实时的日线分析股票的小东西
目前构建了两个表：
表 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
880000012007-08-3137.6536.9538.13817383465528.8
890000012007-08-3037.2836.5838.0837.724682791801.16
900000012007-08-2937.4836.637.6836.8426720898924.54
910000012007-08-2838.5237.538.837.99313335118790.97
920000012007-08-2739.5838.4140.8639.2275096109384.56
930000012007-08-2438.3438.240.0239.6389456153840.25
940000012007-08-2338.437.738.838.39264577100479.57
950000012007-08-2237.8537.6339.5838.59315341122444.39
960000012007-08-2138.4537.739.2638.56335990129941.61
970000012007-08-20363638.1537.9366385137115.49
980000012007-08-1735.9934.3836.934.88420685149258.8
990000012007-08-1638.0235.7738.0536.25385348141712.76
1000000012007-08-1538.437.4538.983824604593615.79
1010000012007-08-1439.236.9939.3338.35349273132011.89
1020000012007-08-1338.5384039.1260558101333.41
1030000012007-08-1039.838.4840.13918578172887.16
1040000012007-08-0938.8238.5340.140.01308030122424.2
1050000012007-08-0838.33840.538.82261425102744.42
1060000012007-08-0738.9538.2739.4539.115632260829.17
1070000012007-08-0638.9938.2639.8939.18258177100531.83
1080000012007-08-033837.8839.5539.11438271170625.75
1090000012007-08-0235.335.337.8537.7396583145256.61
1100000012007-08-0136.2534.4137.135.01291433104275.65
1110000012007-07-3133.8533.6536.436.23418203147946.58
1120000012007-07-3033.733.635.0834.224488284494.16
1130000012007-07-2734.7533.534.8533.85337391114857.79
1140000012007-07-263534.7535.534.9926751093909.2
1150000012007-07-2533.3633.3635.3835.2348526120782.08
1160000012007-07-2432.9832.534.6533.36349242117778.08
1170000012007-07-2333.431.7633.432.95528016172864.06
1180000012007-07-1929.929.6931.2930.4830302893252.64
1190000012007-07-182928.6331.230.24563889170325.63
1200000012007-07-1727.427.229.1829.1532957193194.61
1210000012007-07-1628.226.5328.226.5517613547827.18
1220000012007-07-132928.032928.357528821298.45
1230000012007-07-1228.2528.2529.1728.7515205743750.27
1240000012007-07-1128.428.0128.6628.1211424732276.17
1250000012007-07-10282829.3528.4533547096413.51
1260000012007-07-0927.32728.528.127259576071.62
1270000012007-07-0625.525.4127.1227.01440714116687.33
1280000012007-07-0525.725.1426.8725.237158797198.99
1290000012007-07-0427.4526.127.5526.224396065271.05
1300000012007-07-0327.5526.7328.1527.2325253269364.51
1310000012007-07-0226.8126.5727.8627.432376187561.47
1320000022007-08-3133.5833.2134.333.8754160253335.47
1330000022007-08-3034.0232.9534.833.58792306265950.36
1340000022007-08-2935.833.8836.89341303931458531.53
1350000022007-08-2334.4634.1535.5834.791330811465167.15
1360000022007-08-2233.333.335.834919716316670
1370000022007-08-2133.432.7333.7532.93835819277357.15
1380000022007-08-2031.8831.8833.2833.07800844262081.94
1390000022007-08-1731.7530.732.5830.85964731303718.93
1400000022007-08-1632.831.933.5832.081089108352849.76
1410000022007-08-1533.6633.1534.533.72948210321646.23
1420000022007-08-1432.9931.933.6633.31074049351751.65
1430000022007-08-1332.9832.434.0133.13872838288910.41
1440000022007-08-103533.093533.761346284456189.79
1450000022007-08-0932.632.0635.4835.341496209499703.04
1460000022007-08-0832.8531.6133.0932.271070375344771.71
1470000022007-08-0733.8932.333.8933.381097746363393.45
1480000022007-08-063432.934.433.971110633373180.56
1490000022007-08-0331.8131.6834.733.81325159439760.33
1500000022007-08-0229.129.131.5731.57936991281905.35
1510000022007-08-0128.327.8129.928.71023759296752.99
1520000022007-07-3128.42828.728.3722511204871.2
1530000022007-07-3026.8626.829.5528.49983216280251.08
1540000022007-07-272726.627.4827.3706594190950.61
1550000022007-07-2627.3626.527.9827.41853793231195.59
1560000022007-07-2527.3526.427.427.25815972219887.46
1570000022007-07-2426.572627.8527.05912813246848.43
1580000022007-07-2326.225.8227.5826.361332819354001.2
1590000022007-07-2023.123.0125.6425.641214986303793.43
1600000022007-07-192322.8723.8723.31548016128114.84
1610000022007-07-1823.1322.9824.523.471313205309351.27
1620000022007-07-1720.5420.422.6222.62979416214194.95
1630000022007-07-1621.320.4821.7320.5646394398214.15
1640000022007-07-1221.220.921.7821.26547049117001.77
1650000022007-07-1120.6520.6521.7421.2984220210237.35
1660000022007-07-1020.1820.1521.120.521599870331180.74
1670000022007-07-0919.4219.4220.219.93735802146673.48
1680000022007-07-0618.518.1119.519.3850872596023.92
1690000022007-07-051918.519.4518.5642259779640
1700000022007-07-0419.451920.319.32677896133945.35
1710000022007-07-0318.7618.7619.519.41526655101104.81
1720000022007-07-021918.0219.1918.64570264106378.98
初看简单，搞起来发现有好多难点，看来又是一次学习sql语法好机会
问题1:要查找出10天内涨幅 <20%的股票，(注：当天涨幅=最后一个交易日收盘价/前一个交易日-1)
问题2:求某股收盘价的5日均线MA，2007-08-31的均线应该是包括2007-08-31在内的最近5个交易日的收盘均价
望各位高手不吝指教
[解决办法]
建一个视图，列出你需要的条件，然后根据条件查询，可以解决你第一个问题
至于第二个问题，我不太懂股票，所以均线的概念不懂，帮不到你，不过肯定可以用视图或者存储过程解决
[解决办法]
顶！
[解决办法]
问题一:
--表 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.stockCode
select 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.stockCode
select [计算区间]=convert(varchar(10),a.mintdate,120)+ '~ '+convert(varchar(10),a.maxtdate,120),b.*
from #T3 a
left join #stockcode b
on a.stockCode=b.stockCode
drop table #stockprice,#T1,#T2,#T3