一个很强的sql

--五子棋大赛 标准16×16棋盘 --只要编写一个下棋的存储过程,在存储过程中可以通过表xq获得前面的下棋步骤。 --表xq 字段 id 第几步 x、y横纵坐标决定落子位置,v 下棋者 --存储过程中需要赋值给@x,@y作为本次下棋的落点。 --累计下棋时间超过300秒算超时。 --落子超界或重复落子犯规认输。if(object_id('five') isnotnull) dropprocedure five gocreateprocedure five(@eidnvarchar(100),@widnvarchar(100)) asbegindeclare@iint,@jint,@sqlnvarchar(4000),@dtdatetimedeclare@xqtable(id intidentity,x int,y int,v nvarchar(100))--下棋步骤,V表下棋者,用0和1表示declare@xint,@yint,@vnvarchar(100) --每次的下棋declare@vtint,@vt1int,@vt2int--下棋者的用时declare@js1int,@js2int,@js3int,@js4int--四个方向计数--初始化下棋步骤if(object_id(@eid+'_xq') isnull) beginprint'机器人:'+@eid+'不存在!!!'returnendif(object_id(@wid+'_xq') isnull) beginprint'机器人:'+@wid+'不存在!!!'returnendif(object_id('xq') isnotnull) droptable xq createtable xq(id intidentity,x int,y int,v nvarchar(100)) -- 用于传递给下棋者--开始下棋select@i=0,@vt=0,@vt1=0,@vt2=0--每人300秒while@ibetween0and255begin--清理下棋步骤,获得当前下棋者delete xq insert xq(x,y,v) select x,y,v from@xqselect@v=case@vwhen@eidthen@widelse@eidend,@i=@i+1--下棋select@sql='exec '+@v+'_xq @x out,@y out',@dt=getdate() exec sp_executesql @sql,N'@x int output,@y int output',@x=@x output,@y=@y output if@v=@eidset@vt1=@vt1+datediff(s,@dt,getdate()) elseset@vt2=@vt2+datediff(s,@dt,getdate()) --判断超时if@vt1>@vtor@vt2>@vtbeginprint'机器人:'+@v+'超时认输!'returnend--判断是否犯规if@xnotbetween1and16or@ynotbetween1and16orexists(select1from@xqwhere x=@xand y=@y) beginprint'机器人:'+@v+'落子犯规认输!'returnend--判断是否赢insert@xqselect@x,@y,@vselect@js1=0,@js2=0,@js3=0,@js4=0,@j=-5while@j<4BEGINset@j=@j+1ifexists(select1from@xqwhere y=@y+@jand x =@x+@jand@v= v) set@js1=@js1+1elseset@js1=0ifexists(select1from@xqwhere y=@y+@jand x =@xand@v= v) set@js2=@js2+1elseset@js1=0ifexists(select1from@xqwhere y=@yand x =@x+@jand@v= v) set@js3=@js3+1elseset@js1=0ifexists(select1from@xqwhere y=@y-@jand x =@x-@jand@v= v) set@js4=@js4+1elseset@js1=0if@js1=5or@js2=5or@js3=5or@js4=5select@i=@i+1000,@j=@j+100ENDendif@i<1000beginprint@eid+' vs + '+@wid+' 平局!'returnendprintchar(10)+' 机器人:'+@v+' 在第'+rtrim(@i-1000)+'手时获胜!'print'┏┳┳┳┳┳┳┳┳┳┳┳┳┳┳┳┳┓'select@i=0,@j=@j-100while@i<16beginselect@i=@i+1,@sql='',@j=0while@j<16beginselect@j=@j+1,@v=-1select@v= v from@xqwhere x=@jand y =@iif@i=@yand@j=@xselect@sql=@sql+case@vwhen@eidthen''when@widthen''else''endelseselect@sql=@sql+case@vwhen@eidthen''when@widthen''else''endendprint@sql+''endprint'┗┻┻┻┻┻┻┻┻┻┻┻┻┻┻┻┻┛'endgoif(object_id('cson1_xq') isnotnull) dropprocedure cson1_xq gocreateprocedure cson1_xq(@xint output,@yint output) asbegindeclare@iintselect@i=5while@i<16ifexists(select1from xq where x =@iand y =@i ) set@i=@i+1elsebeginselect@x=@i ,@y=@ireturnendselect@x=@i,@y=@iendgoif(object_id('cson2_xq') isnotnull) dropprocedure cson2_xq gocreateprocedure cson2_xq(@xint output,@yint output) asbegindeclare@iintselect@i=7while@i<16ifexists(select1from xq where x =6and y =@i-1 ) set@i=@i+1elsebeginselect@x=6 ,@y=@i-1returnendselect@x=@i,@y=@iendgoset nocount onexec five 'cson1','cson2'/* 机器人:cson2 在第10手时获胜! ┏┳┳┳┳┳┳┳┳┳┳┳┳┳┳┳┳┓ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋●╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋■╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋■●╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋■╋●╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋■╋╋●╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋□╋╋╋●╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┣╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋╋┫ ┗┻┻┻┻┻┻┻┻┻┻┻┻┻┻┻┻┛ */

posted on 2008-05-05 14:08  超级菜鸟  阅读(209)  评论(0编辑  收藏  举报

导航