--下面是我用SQL实现生活中常玩的24点算法,与大家分享
--代码写得比较匆忙,也许还有许多地方考虑不周,也希望大家帮我多多测试,发现BUG了也告诉我者,本人不胜感激.
/*
--引用者请保留此信息
功能：输入1-24范围的四个整数，输出对应的表达式
创建时间：2007-05-16
作者:wgsasd311(自强不息）
说明：参数范围只能是1-23，超出此范围没有考虑，可能会报错。
*/
create proc p_calc_24
@i1 float,
@i2 float,
@i3 float,
@i4 float
as
SET ANSI_WARNINGS off
SET ARITHABORT off
set nocount on
declare @op table (col char(1))
insert @op values( '+ ')
insert @op values( '- ')
insert @op values( '* ')
insert @op values( '/ ')
declare @tb table (id int identity(1,1),col float)
insert @tb values(@i1)
insert @tb values(@i2)
insert @tb values(@i3)
insert @tb values(@i4)
declare @sql nvarchar(400),@sql1 varchar(100),@sql2 varchar(100),@rult float
declare @op1 char,@op2 char,@op3 char,@i int,@t char(4)
declare cur cursor for
select col1,col2,col3,col4,op1,op2,op3 from
(select a.col col1,b.col col2,c.col col3,d.col col4
from @tb a,@tb b,@tb c,@tb d where b.id <> a.id
and c.id not in(a.id,b.id) and d.id not in(a.id,b.id,c.id) ) a,
(select a.col op1,b.col op2,c.col op3 from @op a,@op b,@op c) b
open cur
fetch next from cur into @i1,@i2,@i3,@i4,@op1,@op2,@op3
while @@fetch_status=0
begin
set @sql1= ' '
set @sql2= ' '
if @op2= '* ' and @op1 in( '+ ', '- ')
set @sql1= '(@i1 '[email protected]+ '@i2)[email protected] '[email protected]+ '@i4 '
else if @op2= '/ ' and @op1 in( '+ ', '- ')
begin
set @sql1= '(@i1 '[email protected]+ '@i2)[email protected] '[email protected]+ '@i4 '
set @sql2= '@i3/(@i1 '[email protected]+ '@i2) '[email protected]+ '@i4 '
end
else if @op2 in( '+ ', '- ') and @op3= '* '
set @sql1= '(@i1 '[email protected]+ '@i2 '[email protected]+ '@i3)[email protected] '
else if @op2 in( '+ ', '- ') and @op3= '/ '
begin
set @sql1= '(@i1 '[email protected]+ '@i2 '[email protected]+ '@i3)[email protected] '
set @sql2= '@i4/(@i1 '[email protected]+ '@i2 '[email protected]+ '@i3) '
end
else
set @sql1= '@i1 '[email protected]+ '@i2 '[email protected]+ '@i3 '[email protected]+ '@i4 '
set @sql= 'set @rult= '[email protected]
exec sp_executesql @sql ,N '@rult float out,@i1 float,@i2 float,@i3 float,@i4 float ',@rult out,@i1,@i2,@i3,@i4
if cast(@rult as decimal(9,4))=24
begin
set @sql1=replace(@sql1, '@i1 ',cast(@i1 as varchar))
set @sql1=replace(@sql1, '@i2 ',cast(@i2 as varchar))
set @sql1=replace(@sql1, '@i3 ',cast(@i3 as varchar))
set @sql1=replace(@sql1, '@i4 ',cast(@i4 as varchar))