sql 写的24点游戏--新手版(原创)

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