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
go

create proc p_ww
@int1 money,
@int2 money,
@int3 money,
@int4 money,
@result dec(18,2)
as
begin
set nocount on
select DISTINCT a.id [aid],b.id [bid],c.id [cid],d.id [did] into #wei
 from (select @int1 [id]
  union all select @int2
  union all select @int3
  union all select @int4) a,(select @int1 [id]
  union all select @int2
  union all select @int3
  union all select @int4) b,(select @int1 [id]
  union all select @int2
  union all select @int3
  union all select @int4) c,(select @int1 [id]
  union all select @int2
  union all select @int3
  union all select @int4) d
where dbo.f_ww(cast(a.id as varchar(10))+','+ cast(b.id as varchar(10))+','+ cast(c.id as varchar(10))+','+ cast(d.id as varchar(10)),
cast(@int1 as varchar(10))+','+cast(@int2 as varchar(10))+','+cast(@int3 as varchar(10))+','+cast(@int4 as varchar(10)))='T'

create table #show(mula varchar(100),result money)

declare @count1 varchar(10),@count2 varchar(10),@count3 varchar(10)
declare @operator1 varchar(2),@operator2 varchar(2),@operator3 varchar(2)
set @count1='+,-,*,/,'

while(charindex(',',@count1)>0)
begin
select @operator1=substring(@count1,0,charindex(',',@count1)),
 @count1=substring(@count1,charindex(',',@count1)+1,len(@count1))
set @count2='+,-,*,/,'

 while(charindex(',',@count2)>0)
 begin
 select @operator2=substring(@count2,0,charindex(',',@count2)),
  @count2=substring(@count2,charindex(',',@count2)+1,len(@count2))
 set @count3='+,-,*,/,'
 
  while(charindex(',',@count3)>0)
  begin
   select @operator3=substring(@count3,0,charindex(',',@count3)),
    @count3=substring(@count3,charindex(',',@count3)+1,len(@count3))
declare @sql nvarchar(4000)
--a/(b/(c/d))
select @sql='insert into #show select replace(cast(aid as varchar(10))+@stroperator1+''(''+cast(bid as varchar(10))+@stroperator2+''(''+cast(cid as varchar(10))+@stroperator3+cast(did as varchar(10))+''))'',''.00'',''''),
aid'+@operator1+'(bid'+@operator2+'(cid'+@operator3+'did))
from #wei where case when @stroperator3=''/''and did=0 then null
when @stroperator2=''/''and cid'+@operator3+'did=0 then null
when @stroperator1=''/''and bid'+@operator2+'(cid'+@operator3+'did)=0 then null
else aid'+@operator1+'(bid'+@operator2+'(cid'+@operator3+'did)) end='+rtrim(@result)
,@sql=@sql+char(10)+ --a/((b/c)/d)
'insert into #show select replace(cast(aid as varchar(10))+@stroperator1+''((''+cast(bid as varchar(10))+@stroperator2+cast(cid as varchar(10))+'')''+@stroperator3+cast(did as varchar(10))+'')'',''.00'',''''),
aid'+@operator1+'((bid'+@operator2+'cid)'+@operator3+'did)
from #wei where case when @stroperator3=''/''and did=0 then null
when @stroperator2=''/''and cid=0 then null
when @stroperator1=''/''and ((bid'+@operator2+'cid)'+@operator3+'did)=0 then null
else aid'+@operator1+'((bid'+@operator2+'cid)'+@operator3+'did) end='+rtrim(@result)
,@sql=@sql+char(10)+ --((a/b)/c)/d
'insert into #show select replace(''((''+cast(aid as varchar(10))+@stroperator1+cast(bid as varchar(10))+'')''+@stroperator2+cast(cid as varchar(10))+'')''+@stroperator3+cast(did as varchar(10)),''.00'',''''),
((aid'+@operator1+'bid)'+@operator1+'cid)'+@operator1+'did
from #wei where case when @stroperator3=''/''and did=0 then null
when @stroperator2=''/''and cid=0 then null
when @stroperator1=''/''and ((bid'+@operator2+'cid)'+@operator3+'did)=0 then null
else ((aid'+@operator1+'bid)'+@operator1+'cid)'+@operator1+'did end='+rtrim(@result)
,@sql=@sql+char(10)+ --(a/(b/c))/d
'insert into #show select replace(''(''+cast(aid as varchar(10))+@stroperator1+''(''+cast(bid as varchar(10))+@stroperator2+cast(cid as varchar(10))+''))''+@stroperator3+cast(did as varchar(10)),''.00'',''''),
(aid'+@operator1+'(bid'+@operator2+'cid))'+@operator3+'did
from #wei where case when @stroperator3=''/''and did=0 then null
when @stroperator2=''/''and cid=0 then null
when @stroperator1=''/''and bid'+@operator2+'cid=0 then null
else (aid'+@operator1+'(bid'+@operator2+'cid))'+@operator3+'did end='+rtrim(@result)
,@sql=@sql+char(10)+ --(a/b)/(c/d)
'insert into #show select replace(''(''+cast(aid as varchar(10))+@stroperator1+cast(bid as varchar(10))+'')''+@stroperator2+''(''+cast(cid as varchar(10))+@stroperator3+cast(did as varchar(10))+'')'',''.00'',''''),(aid'+@operator1+'bid)'+@operator2+'(cid'+@operator3+'did)
from #wei where case when @stroperator3=''/''and did=0 then null
when @stroperator2=''/''and cid'+@operator3+'did=0 then null
when @stroperator1=''/''and did=0 then null
else (aid'+@operator1+'bid)'+@operator2+'(cid'+@operator3+'did) end='+rtrim(@result)

exec sp_executesql
@sql
,N'@stroperator1 varchar(2),@stroperator2 varchar(2),@stroperator3 varchar(2)'
,@stroperator1=@operator1,@stroperator2=@operator2,@stroperator3=@operator3
  end
 end
end

select * from #show
drop table #show
drop table #wei
end

----------------------------
--测试
exec p_ww 1,1,1,13,24
exec p_ww 1,0.6,8,0.8,24
exec p_ww 1,0.6,0.8,6,24

 

 

drop FUNCTION dbo.f_ww
drop proc p_ww