资源描述:
《股票大盘-SQL课程设计》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、setnocountonusestockgocreatetable月末收盘(口期datetimenull,收盘numeric(12,2)null)godeclare@ynchar(12),@yminnchar(12),Oymaxnchar(12)select0ymin=year(min(UWJ))from大盘select0ymax=year(max(HMH))from大盘declare@mintset0m=lset0y=0yminwhile@y<=@ymAxbeginwhile@m<=12begininsertinto月末U攵盘select日期,收盘from大盘wh
2、ere日期=(selectmax(日期)from大盘where(year(日期)=@yandmonth(日期)=@m))setendset@m=lset@y=@y+lendusestockgo一declare@日期datetime,@收盘numeric(12,2)了@副口期datetime,@副收盘numeric(12,2)declare@cint,@cointdeclare@rfloat,@sumfloatset@sum=0set@co=0set0r=lset0c=ldeclaremp_cursorcursorlocalstaticforselectH期,收盘fr
3、om月末收盘openmp_cursorfetchfirstfrommp_cursorinto@Fl期,@4攵盘fetchrelative1frommp_cursorinto@副日期,@副收盘set@r=@副收盘收盘set@co=@co+lset0c=0c+lwhile(@@fetch_status=Oand@c<=12)beginif0r>Oset@sum=@sum+@rfetchrelative11frommp_cursorinto@日期/@斗攵盘fetchrelative1frommp_cursorinto@副日期畐U收盘beginwhilefetch_stat
4、us=Oand@r>0and0c<=12)beginset@r=@副收盘收盘set@sum=@sum+@rset@co=0co+lfetchrelative11frommp_cursorinto@口期/攵盘fetchrelative1frommp_cursorinto@畐iJD期/@副4攵盘endif0r>Obeginprint'第'+C3St(@casvarchmr(12))+'月必盈利,平均盈利:1+cast(@sum/@coasvarchar(12))set@sum=0set@co=lendendfetchabsolute@cfrommp_cursorinto
5、@口期‘@4攵盘set@c=@c+lfetchabsolute@cfrommp_cursorinto@副日期/@副收盘set@r=@副收盘收盘endclosemp_cursordeallocatemp_cursorsetnocountonusestockgocreatetable月末收盘(口期datetimenull,收盘numeric(12,2)null)godeclare@ynchar(12),@yminnchar(12),Oymaxnchar(12)select0ymin=year(min(UWJ))from大盘select0ymax=year(max(HMH
6、))from大盘declare@mintset0m=lset0y=0yminwhile@y<=@ymmx/*年的循坏*/beginwhile@m<=12/*刀的循环★/begininsertinto月末收盘/*将每年的每月的最后一日收盘成交额insert入一张新表里*7select日期,收盘from大盘where日期=(selectmax(Fl期)from大盘where(year(日期)=@yandmonth(tl期)=@m))setendset@m=l/*第二年的新的循坏月份重新重1月開始讀★/set0y=@y+lendusestockgodeclare@FI期d
7、atetime,@收盘numeric(12,2)‘@副
8、~
9、期datetime,0副收盘numeric(12,2)declare@cint,@coint/*@c>@co都是计数器,@c用于输出第几个月,用于求平均数*/declare@rfloat,@sumfloat/*@匕用于计算前后个月收盘额的差额,0sum用于统计每年增长的增长额*/set@sum=0set0co=Oset0r=lset0c=ldeclaremp_cursorcursorlocalstatic/*全局游标★/forselectH期,收盘from月末收盘openmp_cursorfetchfi