sql游戏 + - * /（测试版）
抽空写完啦~写完后发现用了好多知识点~
不容易啊~~
还写了个很好用的函数~~
麻烦大家测测~~
CREATE FUNCTION f_ww(@var varchar(50),@vs varchar(50))
RETURNS char(1)
AS
begin
if len(@var) <> len(@vs)
RETURN 'F '
declare @str varchar(50),@char varchar(10)
declare @varcharint int,@vscharint int
declare @varstr varchar(50),@vsstr varchar(50)
set @str=@var+ ', '
while charindex( ', ',@str)> 0
begin
select @char=substring(@str,0,charindex( ', ',@str)),
@str=substring(@str,charindex( ', ',@str)+1,len(@str)-charindex( ', ',@str)),
@varcharint=0,@vscharint=0,
@varstr= ', '+@var+ ', ', @vsstr= ', '+@vs+ ', '
while(charindex( ', '+@char+ ', ',@varstr))> 0
begin
select @varcharint=@varcharint+1,
@varstr=substring(@varstr,charindex( ', '+@char+ ', ',@varstr)+1+len(@char),len(@varstr))
end
while(charindex( ', '+@char+ ', ',@vsstr))> 0
begin
select @vscharint=@vscharint+1,
@vsstr=substring(@vsstr,charindex( ', '+@char+ ', ',@vsstr)+1+len(@char),len(@vsstr))
end
if(@varcharint <> @vscharint)
RETURN 'F '
end
RETURN 'T '
end
alter proc p_ww
@instr varchar(50),
@incount int
as
begin
declare @thestr varchar(50)
set @thestr=@instr+ ', '
create table #number(id varchar(5))
while (select count(1) from #number) <4
begin
insert into #number select substring(@thestr,0,charindex( ', ',@thestr))
set @thestr=substring(@thestr,charindex( ', ',@thestr)+1,len(@thestr))
end
create table #count(ctid varchar(5))
insert into #count select '+ '
union all select '- '
union all select '* '
union all select '/ '
select identity(int,1,1) id,
aid+b.ctid+ '( '+bid+c.ctid+ '( '+cid+d.ctid+did+ ')) '[mula1],
aid+b.ctid+ '(( '+bid+c.ctid+cid+ ') '+d.ctid+did+ ') '[mula2],
'( '+aid+b.ctid+bid+ ') '+c.ctid+ '( '+cid+d.ctid+did+ ') '[mula3],
'(( '+aid+b.ctid+bid+ ') '+c.ctid+cid+ ') '+d.ctid+did [mula4],
'( '+aid+b.ctid+ '( '+bid+c.ctid+cid+ ')) '+d.ctid+did[mula5]
into #wei
from(select DISTINCT a.id+ '.0 ' [aid],b.id+ '.0 ' [bid],c.id+ '.0 ' [cid],d.id+ '.0 ' [did]
from #number a,#number b,#number c,#number d
where dbo.f_ww(a.id+ ', '+b.id+ ', '+c.id+ ', '+d.id,@instr)= 'T ')a,
#count b,#count c,#count d
create table #show(id int,colname varchar(10),result dec(18,3))
declare @int int,@mula1 varchar(50),@mula2 varchar(50),@mula3 varchar(50),@mula4 varchar(50),@mula5 varchar(50)
set @int=1
while @int <=(select max(id)from #wei)
begin
select @mula1=[mula1],
@mula2=[mula2],
@mula3=[mula3],
@mula4=[mula4],
@mula5=[mula5]
from #wei where id=@int
insert into #show exec( 'select '+@int+ ', ' 'mula1 ' ', '+@mula1)
insert into #show exec( 'select '+@int+ ', ' 'mula2 ' ', '+@mula2)
insert into #show exec( 'select '+@int+ ', ' 'mula3 ' ', '+@mula3)
insert into #show exec( 'select '+@int+ ', ' 'mula4 ' ', '+@mula4)
insert into #show exec( 'select '+@int+ ', ' 'mula5 ' ', '+@mula5)
--if(@@ERROR=8134)
--insert into #show select null
set @int=@int+1
end
select a.id,max(result)[result],max(case when a.colname= 'mula1 ' then b.mula1 end)[mula1],
max(case when a.colname= 'mula2 ' then b.mula2 end)[mula2],
max(case when a.colname= 'mula3 ' then b.mula3 end)[mula3],
max(case when a.colname= 'mula4 ' then b.mula4 end)[mula4],
max(case when a.colname= 'mula5 ' then b.mula5 end)[mula5]
from(select * from #show where result=@incount)a join #wei b
on b.id=a.id
group by a.id
drop table #count
drop table #number
drop table #wei
drop table #show
end
exec p_ww '1,6,6,8 ',24
[解决办法]
怪不得没动静
[解决办法]
NB
[解决办法]
慢
