挑战一下这个麻烦的sql
declare @t table
(
num1 int
,yunsuanfu1 varchar(10)
,num2 int
,yunsuanfu2 varchar(10)
,num3 int
,yunsuanfu3 varchar(10)
,num4 int
,yunsuanfu4 varchar(10)
,num5 int
)
insert into @t values(1,'+',3,'*',4,'+',5,'/',5)
insert into @t values(1,'+',3,'*',4,null,null,null,null)
insert into @t values(1,'+',3,'*',4,'+',5,null,null)
insert into @t values(1,'-',3,'*',4,'+',5,'/',5)
insert into @t values(1,'*',3,'*',4,'+',5,'/',5)
insert into @t values(1,'/',3,'*',4,'+',5,'/',5)
select * from @t
----------------------------
1.列数是固定的
2.并不是每列都有值，但是保证最后一个不为空的列是数字
3.根据数据表中的运算，得出其结果
4.要求具有很大的灵活性，能适应2所说的情况
-------------------------------------
[解决办法]
MSScriptControl.ScriptControl
去执行运算.
[解决办法]
- SQL code
declare @t table ( num1 int ,yunsuanfu1 varchar(10) ,num2 int ,yunsuanfu2 varchar(10) ,num3 int ,yunsuanfu3 varchar(10) ,num4 int ,yunsuanfu4 varchar(10) ,num5 int ) insert into @t values(1,'+',3,'*',4,'+',5,'/',5) insert into @t values(1,'+',3,'*',4,null,null,null,null) insert into @t values(1,'+',3,'*',4,'+',5,null,null) insert into @t values(1,'-',3,'*',4,'+',5,'/',5) insert into @t values(1,'*',3,'*',4,'+',5,'/',5) insert into @t values(1,'/',3,'*',4,'+',5,'/',5) declare @sql varchar(8000)select @sql=isnull(@sql+' union all ','')+'select '+ isnull(ltrim(num1),'')+isnull(yunsuanfu1,'')+ isnull(ltrim(num2),'')+isnull(yunsuanfu2,'')+ isnull(ltrim(num3),'')+isnull(yunsuanfu3,'')+ isnull(ltrim(num4),'')+isnull(yunsuanfu4,'')+ isnull(ltrim(num5),'') from @tprint @sqlexec(@sql)/**select 1+3*4+5/5 union all select 1+3*4 union all select 1+3*4+5 union all select 1-3*4+5/5 union all select 1*3*4+5/5 union all select 1/3*4+5/5 ----------- 141318-10131**/
[解决办法]
- SQL code
create function f_calc(@str varchar(1000)--要计算的表达式)returns sql_variantasbegindeclare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj outif @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@strif @err=0 return(@re)lb_err:exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4),@s varchar(20)set @errb=cast(@err as varbinary(4))exec master..xp_varbintohexstr @errb,@s outreturn('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)endgodeclare @t table ( num1 int ,yunsuanfu1 varchar(10) ,num2 int ,yunsuanfu2 varchar(10) ,num3 int ,yunsuanfu3 varchar(10) ,num4 int ,yunsuanfu4 varchar(10) ,num5 int ) insert into @t values(1,'+',3,'*',4,'+',5,'/',5) insert into @t values(1,'+',3,'*',4,null,null,null,null) insert into @t values(1,'+',3,'*',4,'+',5,null,null) insert into @t values(1,'-',3,'*',4,'+',5,'/',5) insert into @t values(1,'*',3,'*',4,'+',5,'/',5) insert into @t values(1,'/',3,'*',4,'+',5,'/',5) select dbo.f_calc(ltrim(num1)+yunsuanfu1+ltrim(num2)+yunsuanfu2+ltrim(num3)+isnull(yunsuanfu3,'')+isnull(ltrim(num4),'')+isnull(yunsuanfu4,'')+isnull(ltrim(num5),'')) from @t drop function f_calc/* ---------------------------------------------------------------------------------------------------------------- 141318-10132.33333333333333*/
[解决办法]
- SQL code
declare @t table
(
shangping varchar(10)
,num1 int
,yunsuanfu1 varchar(10)
,num2 int
,yunsuanfu2 varchar(10)
,num3 int
,yunsuanfu3 varchar(10)
,num4 int
,yunsuanfu4 varchar(10)
,num5 int
)
insert into @t values('苹果',1,'+',3,'*',4,'+',5,'/',5)
insert into @t values('橘子',1,'+',3,'*',4,null,null,null,null)
insert into @t values('桔子',1,'+',3,'*',4,'+',5,null,null)
insert into @t values('香蕉',1,'-',3,'*',4,'+',5,'/',5)
insert into @t values('菠萝',1,'*',3,'*',4,'+',5,'/',5)
insert into @t values('荔枝',1,'/',3,'*',4,'+',5,'/',5)
declare @sql varchar(8000)
select
@sql=isnull(@sql+' union all ','')+'select '''+
shangping+''','+
isnull(ltrim(num1),'')+isnull(yunsuanfu1,'')+
isnull(ltrim(num2),'')+isnull(yunsuanfu2,'')+
isnull(ltrim(num3),'')+isnull(yunsuanfu3,'')+
isnull(ltrim(num4),'')+isnull(yunsuanfu4,'')+
isnull(ltrim(num5),'')
from @t
--print @sql
exec(@sql)
/**
---- -----------
苹果 14
橘子 13
桔子 18
香蕉 -10
菠萝 13
荔枝 1
**/
正如9楼所说，这种做法除法就是取模，可能会丢精度
[解决办法]
- SQL code
create table t(shangping varchar(10),num1 int,yunsuanfu1 varchar(10),num2 int,yunsuanfu2 varchar(10),num3 int,yunsuanfu3 varchar(10),num4 int,yunsuanfu4 varchar(10),num5 int)insert into t values('苹果',1,'+',3,'*',4,'+',5,'/',5)insert into t values('橘子',1,'+',3,'*',4,null,null,null,null)insert into t values('桔子',1,'+',3,'*',4,'+',5,null,null)insert into t values('香蕉',1,'-',3,'*',4,'+',5,'/',5)insert into t values('菠萝',1,'*',3,'*',4,'+',5,'/',5)insert into t values('荔枝',1,'/',3,'*',4,'+',5,'/',5)---测试结果表select top 0 *,cast(null as float) as ex into t_ex from t
[解决办法]
用clr写字定义函数计算
c#代码，编译为SqlServerProject2.dll
- C# code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDouble Function1(SqlInt32 num1, SqlString yun1, SqlInt32 num2, SqlString yun2, SqlInt32 num3, SqlString yun3, SqlInt32 num4, SqlString yun4, SqlInt32 num5)
{
string exp = string.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}",
num1.IsNull ? "" : num1.Value.ToString(), yun1.IsNull ? "" : yun1.Value,
num2.IsNull ? "" : num2.Value.ToString(), yun2.IsNull ? "" : yun2.Value,
num3.IsNull ? "" : num3.Value.ToString(), yun3.IsNull ? "" : yun3.Value,
num4.IsNull ? "" : num4.Value.ToString(), yun4.IsNull ? "" : yun4.Value,
num5.IsNull ? "" : num5.Value.ToString()
);
object obj = new DataTable().Compute(exp, "");
try
{
return double.Parse(obj.ToString());
}
catch
{
return SqlDouble.Null;
}
}
};
部署和测试代码
- SQL code
--drop FUNCTION dbo.xfn_getvalue --drop ASSEMBLY SqlServerProject2clrCREATE ASSEMBLY SqlServerProject2clr FROM 'E:\sqlclrdata\SqlServerProject2.dll' WITH PERMISSION_SET = UnSAFE;--goCREATE FUNCTION dbo.xfn_getvalue (@num1 int,@yunsuanfu1 nvarchar(10),@num2 int,@yunsuanfu2 nvarchar(10),@num3 int,@yunsuanfu3 nvarchar(10),@num4 int,@yunsuanfu4 nvarchar(10),@num5 int) RETURNS floatAS EXTERNAL NAME SqlServerProject2clr.[UserDefinedFunctions].Function1godeclare @t table(shangping varchar(10),num1 int,yunsuanfu1 varchar(10),num2 int,yunsuanfu2 varchar(10),num3 int,yunsuanfu3 varchar(10),num4 int,yunsuanfu4 varchar(10),num5 int)insert into @t values('苹果',1,'+',3,'*',4,'+',5,'/',5)insert into @t values('橘子',1,'+',3,'*',4,null,null,null,null)insert into @t values('桔子',1,'+',3,'*',4,'+',5,null,null)insert into @t values('香蕉',1,'-',3,'*',4,'+',5,'/',5)insert into @t values('菠萝',1,'*',3,'*',4,'+',5,'/',5)insert into @t values('荔枝',1,'/',3,'*',4,'+',5,'/',5) select *,dbo.xfn_getvalue(num1,yunsuanfu1,num2,yunsuanfu2,num3,yunsuanfu3,num4,yunsuanfu4,num5) as ex from @t/*苹果 1 + 3 * 4 + 5 / 5 14橘子 1 + 3 * 4 NULL NULL NULL NULL 13桔子 1 + 3 * 4 + 5 NULL NULL 18香蕉 1 - 3 * 4 + 5 / 5 -10菠萝 1 * 3 * 4 + 5 / 5 13荔枝 1 / 3 * 4 + 5 / 5 2.33333333333333*/
[解决办法]
其实老贴. 解法有很多，主要思路有三种，
(1)调用其它脚本语言的eval方法
(2)循环加exec实现
(3)自己写算法拆分公式。
下面是调用xsl实现。参见
http://blog.csdn.net/fcuandy/archive/2008/03/28/2226369.aspx
- SQL code
CREATE FUNCTION getEval(@s VARCHAR(100))RETURNS VARCHAR(100)ASBEGINDECLARE @xmlDoc INT,@xslDoc INT,@err INT,@outValue INT,@outStr VARCHAR(100)DECLARE @str VARCHAR(1000),@xsl VARCHAR(8000)SET @str='<?xml version="1.0" encoding="utf-8"?><?xml-stylesheet type="text/xsl" href="test.xsl"?><root>'+@s+'</root>'SET @xsl='<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"><xsl:template match="/"><xsl:script>function eVal(node){ return eval(node.selectSingleNode("//root").text);}</xsl:script><xsl:eval>eVal(this)</xsl:eval></xsl:template></xsl:stylesheet>'--SELECT @str,@xslEXEC @err=sp_OACreate 'Msxml2.DOMDocument.3.0',@xmlDoc OUTEXEC @err=sp_OASetProperty @xmlDoc,'async','false'EXEC @err=sp_OAMethod @xmlDoc,'LoadXML',@outValue OUT,@strEXEC @err=sp_OACreate 'Msxml2.DOMDocument.3.0',@xslDoc OUTEXEC @err=sp_OASetProperty @xslDoc,'async','false'EXEC @err=sp_OASetProperty @xmlDoc,'resolveExternals','false'EXEC @err=sp_OAMethod @xslDoc,'LoadXML',@outValue OUT,@xslEXEC @err=sp_OAMethod @xmlDoc,'transformNode',@outStr OUT,@XslDocEXEC @err=sp_OADestroy @xmlDocEXEC @err=sp_OADestroy @xslDocRETURN @outStrENDGOdeclare @t table(num1 int,yunsuanfu1 varchar(10),num2 int,yunsuanfu2 varchar(10),num3 int,yunsuanfu3 varchar(10),num4 int,yunsuanfu4 varchar(10),num5 int)SET NOCOUNT ONinsert into @t values(1,'+',3,'*',4,'+',5,'/',5)insert into @t values(1,'+',3,'*',4,null,null,null,null)insert into @t values(1,'+',3,'*',4,'+',5,null,null)insert into @t values(1,'-',3,'*',4,'+',5,'/',5)insert into @t values(1,'*',3,'*',4,'+',5,'/',5)insert into @t values(1,'/',3,'*',4,'+',5,'/',5) SET NOCOUNT OFFSELECT *,dbo.getEval( ISNULL(RTRIM(num1),'') + ISNULL(RTRIM(yunsuanfu1),'') + ISNULL(RTRIM(num2),'') + ISNULL(RTRIM(yunsuanfu2),'') + ISNULL(RTRIM(num3),'') + ISNULL(RTRIM(yunsuanfu3),'') + ISNULL(RTRIM(num4),'') + ISNULL(RTRIM(yunsuanfu4),'') + ISNULL(RTRIM(num5),'') )FROM @tGODROP FUNCTION getEVALGO/*1 + 3 * 4 + 5 / 5 14 1 + 3 * 4 NULL NULL NULL NULL 13 1 + 3 * 4 + 5 NULL NULL 18 1 - 3 * 4 + 5 / 5 -10 1 * 3 * 4 + 5 / 5 13 1 / 3 * 4 + 5 / 5 2.33333333333333 */
[解决办法]
- SQL code
create function dbo.js(@bds varchar(1000))
returns float
as
begin
declare @i int,@j int
declare @c1 char(1),@c2 char(1),@c varchar(100)
declare @v1 float,@v2 float,@v float
declare @t table(id int identity(1,1),s varchar(100))
declare @s table(id int identity(1,1),s varchar(100))
declare @sv table(id int identity(1,1),v float)
select @i = 0,@j = len(@bds),@c2 = '',@c = ''
while @i <@j
begin
select @c1 = @c2,@i = @i+1
select @c2 = substring(@bds,@i,1)
if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and charindex(@c1,'.0123456789')=0)
begin select @c = @c + @c2 continue end
if @c <> '' begin insert @t(s) select @c select @c = '' end
if charindex(@c2,'+-)')>0
begin
insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc
delete @s where id >= isnull((select max(id) from @s where s in('(')),0)
if @c2 <> ')' insert @s(s) select @c2
continue
end
if charindex(@c2,'*/')>0
begin
insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) order by id desc
delete @s where id > isnull((select max(id) from @s where s in('(','+','-')),0)
insert @s select @c2
continue
end
if charindex(@c2,'(')>0 insert @s select @c2
end
if @c <> '' insert @t(s) select @c
insert @t(s) select s from @s order by id desc
select @i = 0,@j = max(id) from @t
while @i < @j
begin
select @i = @i + 1
select @c = s from @t where id = @i
if @c = '(' continue
if @c not in('*','-','+','/') begin insert @sv(v) select convert(float,@c) continue end
select @v2 = v from @sv delete @sv where id = (select max(id) from @sv)
select @v1 = v from @sv delete @sv where id = (select max(id) from @sv)
select @v = case @c when '+' then @v1 + @v2 when '-' then @v1 - @v2
when '*' then @v1 * @v2 when '/' then @v1 / @v2 end
insert @sv(v) select @v
end
select @v = v from @sv
return @v
end
go
declare @t table
(
num1 int
,yunsuanfu1 varchar(10)
,num2 int
,yunsuanfu2 varchar(10)
,num3 int
,yunsuanfu3 varchar(10)
,num4 int
,yunsuanfu4 varchar(10)
,num5 int
)
insert into @t values(1,'+',3,'*',4,'+',5,'/',5)
insert into @t values(1,'+',3,'*',4,null,null,null,null)
insert into @t values(1,'+',3,'*',4,'+',5,null,null)
insert into @t values(1,'-',3,'*',4,'+',5,'/',5)
insert into @t values(1,'*',3,'*',4,'+',5,'/',5)
insert into @t values(1,'/',3,'*',4,'+',5,'/',5)
select
dbo.js(
ISNULL(ltrim(num1),'')
+ isnull(yunsuanfu1,'')
+ISNULL(ltrim(num2),'')
+ isnull(yunsuanfu2,'')
+ISNULL(ltrim(num3),'')
+ isnull(yunsuanfu3,'')
+ISNULL(ltrim(num4),'')
+ isnull(yunsuanfu4,'')
+ISNULL(ltrim(num5),'')
) from @t
/*
14
13
18
-10
13
2.33333333333333
*/
[解决办法]
个人比较推荐近身剪的方法，在.NET中写一个CLR的类，然后在SQL2005中调用。
下面是我见过我认为是最好的表达式计算类。该代码用正则表达编写，源于 VB2005技术内幕 ，支持的函数非常多，比WINDOWS的计算器还要强。
- VB.NET code
Imports System.Text.RegularExpressions
Module EvaluateModule
Function Evaluate(ByVal expr As String) As Double
' A number is a sequence of digits optionally followed by a dot and
' another sequence of digits. The number in parenthesis in order to
' define an unnamed group.
Const Num As String = "(\-?\d+\.?\d*)"
' List of 1-operand functions.
Const Func1 As String = "(exp|log|log10|abs|sqr|sqrt|sin|cos|tan|asin|acos|atan)"
' List of 2-operand functions.
Const Func2 As String = "(atan2)"
' List of N-operand functions.
Const FuncN As String = "(min|max)"
' List of predefined constants.
Const Constants As String = "(e|pi)"
' Define one Regex object for each supported operation.
' They are outside the loop, so that they are compiled only once.
' Binary operations are defined as two numbers with a symbol between them
' optionally separated by spaces.
Dim rePower As New Regex(Num & "\s*(\^)\s*" & Num)
Dim reAddSub As New Regex(Num & "\s*([-+])\s*" & Num)
Dim reMulDiv As New Regex(Num & "\s*([*/])\s*" & Num)
' These Regex objects resolve call to functions. (Case insensitivity.)
Dim reFunc1 As New Regex(Func1 & "\(\s*" & Num & "\s*\)", _
RegexOptions.IgnoreCase)
Dim reFunc2 As New Regex(Func2 & "\(\s*" & Num & "\s*,\s*" & Num _
& "\s*\)", RegexOptions.IgnoreCase)
Dim reFuncN As New Regex(FuncN & "\((\s*" & Num & "\s*,)+\s*" & Num _
& "\s*\)", RegexOptions.IgnoreCase)
' This Regex object drop a + when it follows an operator.
Dim reSign1 As New Regex("([-+/*^])\s*\+")
' This Regex object converts a double minus into a plus.
Dim reSign2 As New Regex("\-\s*\-")
' This Regex object drops parenthesis around a number.
' (must not be preceded by an alphanum char (it might be a function name)
Dim rePar As New Regex("(? <![A-Za-z0-9])\(\s*([-+]?\d+.?\d*)\s*\)")
' A Regex object that tells that the entire expression is a number
Dim reNum As New Regex("^\s*[-+]?\d+\.?\d*\s*$")
' The Regex object deals with constants. (Requires case insensitivity.)
Dim reConst As New Regex("\s*" & Constants & "\s*", _
RegexOptions.IgnoreCase)
' This resolves predefined constants. (Can be kept out of the loop.)
expr = reConst.Replace(expr, AddressOf DoConstants)
' Loop until the entire expression becomes just a number.
Do Until reNum.IsMatch(expr)
Dim saveExpr As String = expr
' Perform all the math operations in the source string.
' starting with operands with higher operands.
' Note that we continue to perform each operation until there are
' no matches, because we must account for expressions like (12*34*56)
' Perform all power operations.
Do While rePower.IsMatch(expr)
expr = rePower.Replace(expr, AddressOf DoPower)
Loop
' Perform all divisions and multiplications.
Do While reMulDiv.IsMatch(expr)
expr = reMulDiv.Replace(expr, AddressOf DoMulDiv)
Loop
' Perform functions with variable numbers of arguments.
Do While reFuncN.IsMatch(expr)
expr = reFuncN.Replace(expr, AddressOf DoFuncN)
Loop
' Perform functions with 2 arguments.
Do While reFunc2.IsMatch(expr)
expr = reFunc2.Replace(expr, AddressOf DoFunc2)
Loop
' 1-operand functions must be processed last to deal correctly with
' expressions such as SIN(ATAN(1)) before we drop parenthesis
' pairs around numbers.
Do While reFunc1.IsMatch(expr)
expr = reFunc1.Replace(expr, AddressOf DoFunc1)
Loop
' Discard + symbols (unary pluses)that follow another operator.
expr = reSign1.Replace(expr, "$1")
' Simplify 2 consecutive minus signs into a plus sign.
expr = reSign2.Replace(expr, "+")
' Perform all additions and subtractions.
Do While reAddSub.IsMatch(expr)
expr = reAddSub.Replace(expr, AddressOf DoAddSub)
Loop
' attempt to discard parenthesis around numbers. We can do this
expr = rePar.Replace(expr, "$1")
' if the expression didn't change, we have a syntax error.
' this serves to avoid endless loops
If expr = saveExpr Then
' if it didn't work, exit with syntax error exception.
Throw New SyntaxErrorException()
End If
Loop
' Return the expression, which is now a number.
Return CDbl(expr)
End Function
' These functions evaluate the actual math operations.
' In all cases the Match object on entry has groups that identify
' the two operands and the operator.
Function DoConstants(ByVal m As Match) As String
Select Case m.Groups(1).Value.ToUpper
Case "PI"
Return Math.PI.ToString
Case "E"
Return Math.E.ToString
End Select
End Function
Function DoPower(ByVal m As Match) As String
Dim n1 As Double = CDbl(m.Groups(1).Value)
Dim n2 As Double = CDbl(m.Groups(3).Value)
' Group(2) is always the ^ character in this version.
Return (n1 ^ n2).ToString
End Function
Function DoMulDiv(ByVal m As Match) As String
Dim n1 As Double = CDbl(m.Groups(1).Value)
Dim n2 As Double = CDbl(m.Groups(3).Value)
Select Case m.Groups(2).Value
Case "/"
Return (n1 / n2).ToString
Case "*"
Return (n1 * n2).ToString
End Select
End Function
Function DoAddSub(ByVal m As Match) As String
Dim n1 As Double = CDbl(m.Groups(1).Value)
Dim n2 As Double = CDbl(m.Groups(3).Value)
Select Case m.Groups(2).Value
Case "+"
Return (n1 + n2).ToString
Case "-"
Return (n1 - n2).ToString
End Select
End Function
' These functions evaluate functions.
Function DoFunc1(ByVal m As Match) As String
' function argument is 2nd group.
Dim n1 As Double = CDbl(m.Groups(2).Value)
' function name is 1st group.
Select Case m.Groups(1).Value.ToUpper
Case "EXP"
Return Math.Exp(n1).ToString
Case "LOG"
Return Math.Log(n1).ToString
Case "LOG10"
Return Math.Log10(n1).ToString
Case "ABS"
Return Math.Abs(n1).ToString
Case "SQR", "SQRT"
Return Math.Sqrt(n1).ToString
Case "SIN"
Return Math.Sin(n1).ToString
Case "COS"
Return Math.Cos(n1).ToString
Case "TAN"
Return Math.Tan(n1).ToString
Case "ASIN"
Return Math.Asin(n1).ToString
Case "ACOS"
Return Math.Acos(n1).ToString
Case "ATAN"
Return Math.Atan(n1).ToString
End Select
End Function
Function DoFunc2(ByVal m As Match) As String
' function arguments are 2nd and 3rd group.
Dim n1 As Double = CDbl(m.Groups(2).Value)
Dim n2 As Double = CDbl(m.Groups(3).Value)
' function name is 1st group.
Select Case m.Groups(1).Value.ToUpper
Case "ATAN2"
Return Math.Atan2(n1, n2).ToString
End Select
End Function
Function DoFuncN(ByVal m As Match) As String
' function arguments are from group 2 onward.
Dim args As New ArrayList()
Dim i As Integer = 2
' Load all the arguments into the array.
Do While m.Groups(i).Value <> ""
' Get the argument, replace any comma to space, and convert to double.
args.Add(CDbl(m.Groups(i).Value.Replace(","c, " "c)))
i += 1
Loop
' function name is 1st group.
Select Case m.Groups(1).Value.ToUpper
Case "MIN"
args.Sort()
Return args(0).ToString
Case "MAX"
args.Sort()
Return args(args.Count - 1).ToString
End Select
End Function
End Module
[解决办法]
- SQL code
DECLARE @v CHAR(4), @i INT, @j INT, @k INT, @m INTSELECT @v = '+-*/', @i = 0 PRINT 'SELECT CASE ISNULL(yunsuanfu1, '''') + ISNULL(yunsuanfu2, '''')+ ISNULL(yunsuanfu3, '''') + ISNULL(yunsuanfu4, '''')WHEN '''' THEN num1' WHILE @i < 4 BEGIN SELECT @j = 0, @i = @i + 1 PRINT 'WHEN ''' + SUBSTRING(@v, @i, 1) + ''' THEN num1' + SUBSTRING(@v, @i, 1) + 'num2' WHILE @j < 4 BEGIN SELECT @k = 0, @j = @j + 1 PRINT 'WHEN ''' + SUBSTRING(@v, @i, 1) + SUBSTRING(@v, @j, 1) + ''' THEN num1' + SUBSTRING(@v, @i, 1) + 'num2' + SUBSTRING(@v, @j, 1) + 'num3' WHILE @k < 4 BEGIN SELECT @m = 0, @k = @k + 1 PRINT 'WHEN ''' + SUBSTRING(@v, @i, 1) + SUBSTRING(@v, @j, 1) + SUBSTRING(@v, @k, 1) + ''' THEN num1' + SUBSTRING(@v, @i, 1) + 'num2' + SUBSTRING(@v, @j, 1) + 'num3' + SUBSTRING(@v, @k, 1) + 'num4' WHILE @m < 4 BEGIN SELECT @m = @m + 1 PRINT 'WHEN ''' + SUBSTRING(@v, @i, 1) + SUBSTRING(@v, @j, 1) + SUBSTRING(@v, @k, 1) + SUBSTRING(@v, @m, 1) + ''' THEN num1' + SUBSTRING(@v, @i, 1) + 'num2' + SUBSTRING(@v, @j, 1) + 'num3' + SUBSTRING(@v, @k, 1) + 'num4' + SUBSTRING(@v, @m, 1) + 'num5' END END END ENDPRINT 'ENDFROM @t'