declare @a decimal(10,6),
@b decimal(10,6),
@c decimal(10,6),
@d decimal(10,6)
select @a=2,@b=5,@c=5,@d=8---四个数字
declare @t table(id int identity,num decimal(10,2))
insert @t select @a union all select @b union all select @c union all select @d
declare @result table(str1 varchar(100),str2 varchar(100),result decimal(10,6))
insert @result select rtrim(id)+'#'+rtrim(cast(num as int)),rtrim(cast(num as int)),num from @t
declare @i int
set @i=1
while @i<4
begin
insert @result select A.str1+'+'+rtrim(id)+'#'+rtrim(cast(num as int)),A.str2+'+'+rtrim(cast(num as int)),result+num from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0
/*这边是消除重复答案，因为速慢，而注释
and A.str2+'+'+rtrim(cast(num as int)) not in (select str2 from @result)
and rtrim(cast(num as int))+'+'+A.str2 not in (select str2 from @result)
*/
insert @result select A.str1+'-'+rtrim(id)+'#'+rtrim(cast(num as int)),A.str2+'-'+rtrim(cast(num as int)),result-num from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0
insert @result select case when charindex('-',str2)>0
then rtrim(id)+'#'+rtrim(cast(num as int))+'-('+A.str1+')'
else rtrim(id)+'#'+rtrim(cast(num as int))+'-'+A.str1
end,
case when charindex('-',str2)>0
then rtrim(cast(num as int))+'-('+A.str2+')'
else rtrim(cast(num as int))+'-'+A.str2
end
,num-result from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0
insert @result select
case when charindex('+',A.str1)>0 or charindex('-',A.str1)>0
then '('+A.str1+')'+rtrim(id)+'#'+rtrim(cast(num as int))
else A.str1+''+rtrim(id)+'#'+rtrim(cast(num as int))
end,
case when charindex('+',A.str2)>0 or charindex('-',A.str2)>0
then '('+A.str2+')'+rtrim(cast(num as int))
else A.str2+''+rtrim(cast(num as int))
end,
resultnum from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0
/这边是消除重复答案，因为速慢，而注释
and (case when charindex('+',A.str2)>0 or charindex('-',A.str2)>0
then '('+A.str2+')'+rtrim(cast(num as int))
else A.str2+''+rtrim(cast(num as int))
end) not in (select str2 from @result)
and (case when charindex('+',A.str2)>0 or charindex('-',A.str2)>0
then rtrim(cast(num as int))+'('+A.str2+')'
else rtrim(cast(num as int))+''+A.str2
end) not in (select str2 from @result)
*/
insert @result select
case when (charindex('+',A.str1)>0 or charindex('-',A.str1)>0) and charindex('(',str1)=0
then '('+A.str1+')/'+rtrim(id)+'#'+rtrim(cast(num as int))
else A.str1+'/'+rtrim(id)+'#'+rtrim(cast(num as int))
end,
case when charindex('+',A.str2)>0 or charindex('-',A.str2)>0 and charindex('(',str2)=0
then '('+A.str2+')/'+rtrim(cast(num as int))
else A.str2+'/'+rtrim(cast(num as int))
end,
result/num from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0 and num<>0
insert @result select
case when (charindex('+',A.str1)>0 or charindex('-',A.str1)>0) and charindex('(',str1)=0
then rtrim(id)+'#'+rtrim(cast(num as int))+'/('+A.str1+')'
else rtrim(id)+'#'+rtrim(cast(num as int))+'/'+A.str1
end,
case when ((charindex('+',A.str2)>0 or charindex('-',A.str2)>0) and charindex('(',str2)=0) or charindex('/',str2)>0 or charindex('*',str2)>0
then rtrim(cast(num as int))+'/('+A.str2+')'
else rtrim(cast(num as int))+'/'+A.str2
end,
num/result from @result A,@t B where charindex(rtrim(id)+'#'+rtrim(cast(num as int)),str1)=0 and result<>0
set @i=@i+1
end
select distinct cast(rtrim(str2) as varchar(20)) 计算公式 from @result where result=24 and len(str2)-len(replace(replace(replace(replace(st
---------------------------------------------------------------
修改方案：
在每一个查询插入表@result的数据加上 distinct
可以使原@result表记录从23076条减少到8200多条，查询时间差不多减少一半，
至于其他方案正在攻克.....
---------------------------------------------------------------
Create procedure dian24 @a decimal(10,6),
@b decimal(10,6),
@c decimal(10,6),
@d decimal(10,6)
As
declare @t table(id int identity,num decimal(10,2))
insert @t select @a union all select @b union all select @c union all select @d
--生成单项式(A)
declare @temp1 table(str1 varchar(20),str2 varchar(20),result decimal(10,6))
--insert @temp1 select rtrim(id),'.'+rtrim(cast(num as int))+'.',num from @t
insert @temp1 select rtrim(id),rtrim(cast(num as int)),num from @t
declare @temp2 table(str1 varchar(20),str2 varchar(20),result decimal(10,6))
declare @temp3 table(str1 varchar(20),str2 varchar(20),result decimal(10,6))
--生成二项式(A?B))
insert @temp2 select distinct * from (
select A.str1+','+B.str1 as str1,A.str2+'+'+B.str2 as str2,A.result+B.result as result from @temp1 A,@temp1 B where A.str1<>B.str1
union all
select A.str1+','+B.str1,A.str2+'-'+B.str2,A.result-B.result from @temp1 A,@temp1 B where A.str1<>B.str1
union all
select A.str1+','+B.str1,B.str2+'-'+A.str2,B.result-A.result from @temp1 A,@temp1 B where A.str1<>B.str1
union all
select A.str1+','+B.str1,A.str2+''+B.str2,A.resultB.result from @temp1 A,@temp1 B where A.str1<>B.str1
union all
select A.str1+','+B.str1,A.str2+'/'+B.str2,A.result/B.result from @temp1 A,@temp1 B where A.str1<>B.str1
union all
select A.str1+','+B.str1,B.str2+'/'+A.str2,B.result/A.result from @temp1 A,@temp1 B where A.str1<>B.str1
) M
--生成三项式(A?B?C)
insert @temp3 select * from (
select A.str1+','+B.str1 as str1,A.str2+'+'+B.str2 as str2,A.result+B.result as result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,A.str2+'-'+
(case when charindex('-',B.str2)>0 or charindex('+',B.str2)>0 then '('+B.str2+')'else B.str2 end),
A.result-B.result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,B.str2+'-'+A.str2,B.result-A.result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,A.str2+''+
(case when charindex('-',B.str2)>0 or charindex('+',B.str2)>0 then '('+B.str2+')'else B.str2 end),
A.resultB.result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,A.str2+'/('+B.str2+')',A.result/B.result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0 and B.result<>0
union all
select A.str1+','+B.str1,
(case when charindex('-',B.str2)>0 or charindex('+',B.str2)>0 then '('+B.str2+')'else B.str2 end)
+'/'+A.str2,B.result/A.result from @temp1 A,@temp2 B where charindex(','+A.str1+',',','+B.str1+',')=0 and A.result<>0
) M
select distinct str2 from (
--@temp1+@temp3单项式和三项式合并成四项式------
select A.str1+','+B.str1 as str1,A.str2+'+'+B.str2 as str2,A.result+B.result as result from @temp1 A,@temp3 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,A.str2+'-'+
(case when charindex('-',B.str2)>0 or charindex('+',B.str2)>0 then '('+B.str2+')'else B.str2 end),
A.result-B.result from @temp1 A,@temp3 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,B.str2+'-'+A.str2,B.result-A.result from @temp1 A,@temp3 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+','+B.str1,A.str2+''+
(case when charindex('-',B.str2)>0 or charindex('+',B.str2)>0 then '('+B.str2+')'else B.str2 end),
A.resultB.result from @temp1 A,@temp3 B where charindex(','+A.str1+',',','+B.str1+',')=0
union all
select A.str1+