请教一个SQL语句的问题，多谢指点！

Question

--创建生成计算公式的函数
create function f_sql(
@comid varchar(10),
@time char(7),
@expression varchar(20)
)returns varchar(6000)
as
begin
declare @re varchar(8000),@i int,@j int,@str1 varchar(100)
select @re=@expression
,@str1='(select avg(data) from tdata where comid='+@comid
+' and [time]='''+@time+''' and typeid='
,@i=patindex('%#[0-9]%',@re)
while @i>0
begin
set @j=patindex('%[^0-9]%',stuff(@re+'a',1,@i+1,''))
if @j>0
select @re=stuff(@re,@i,@j+1,@str1+substring(@re,@i+1,@j)+')')
,@i=patindex('%#[0-9]%',@re)
else 
set @i=0
end
return(@re)
end
go--计算处理
select id=identity(int,1,1),sql='
 comid='+cast(a.comid as varchar)+'
,biaoid='+cast(c.biaoid as varchar)+'
,data=cast(('+dbo.f_sql(a.comid,a.[time],c.expression)+') as decimal(10,2))
,[time]='''+a.[time]+''''
into #t
from tdata a
join tdatatype b on a.typeid=b.id
,texpression c
group by a.comid,c.biaoid,a.[time],c.expression
order by a.[time],a.comid,c.biaoiddeclare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+' select @'+cast(id as varchar)+'=sql from #t where id='+cast(id as varchar)
,@s3=@s3+'+'' union all select ''+@'+cast(id as varchar)
from #t
select @s1=stuff(@s1,1,1,'')
,@s3=stuff(@s3,1,13,'')
exec('
declare '+@s1+'
'+@s2+'
exec('''+@s3+')
')
drop table #t

Answer 1

--测试--测试数据
create table tdatatype(id int,name varchar(10))
insert tdatatype select 1,'a'
union  all       select 2,'b'
union  all       select 3,'c'
union  all       select 4,'d'create table tdata(comid int,typeid int,data decimal(10,2),[time] char(7))
insert tdata select 1,1,1.4 ,'2004-05'
union  all   select 1,2,0.6 ,'2004-05'
union  all   select 1,3,1.3 ,'2004-05'
union  all   select 1,4,1.1 ,'2004-05'
union  all   select 2,1,0.5 ,'2004-05'
union  all   select 2,2,0.2 ,'2004-05'
union  all   select 2,3,1.8 ,'2004-05'
union  all   select 2,4,0.3 ,'2004-05'
union  all   select 1,1,1.12,'2004-06'
union  all   select 1,2,0.1 ,'2004-06'
union  all   select 1,3,1.7 ,'2004-06'
union  all   select 1,4,1.0 ,'2004-06'create table texpression(biaoid int,expression varchar(20))
insert texpression select 1,'(#1/(#2+#4))*0.5'
union  all         select 2,'1.5 - #1/(#3-#4)'
go--创建生成计算公式的函数
create function f_sql(
@comid varchar(10),
@time char(7),
@expression varchar(20)
)returns varchar(6000)
as
begin
declare @re varchar(8000),@i int,@j int,@str1 varchar(100)
select @re=@expression
,@str1='(select avg(data) from tdata where comid='+@comid
+' and [time]='''+@time+''' and typeid='
,@i=patindex('%#[0-9]%',@re)
while @i>0
begin
set @j=patindex('%[^0-9]%',stuff(@re+'a',1,@i+1,''))
if @j>0
select @re=stuff(@re,@i,@j+1,@str1+substring(@re,@i+1,@j)+')')
,@i=patindex('%#[0-9]%',@re)
else 
set @i=0
end
return(@re)
end
go--计算处理
select id=identity(int,1,1),sql='
 comid='+cast(a.comid as varchar)+'
,biaoid='+cast(c.biaoid as varchar)+'
,data=cast(('+dbo.f_sql(a.comid,a.[time],c.expression)+') as decimal(10,2))
,[time]='''+a.[time]+''''
into #t
from tdata a
join tdatatype b on a.typeid=b.id
,texpression c
group by a.comid,c.biaoid,a.[time],c.expression
order by a.[time],a.comid,c.biaoiddeclare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+cast(id as varchar)+' varchar(8000)'
,@s2=@s2+' select @'+cast(id as varchar)+'=sql from #t where id='+cast(id as varchar)
,@s3=@s3+'+'' union all select ''+@'+cast(id as varchar)
from #t
select @s1=stuff(@s1,1,1,'')
,@s3=stuff(@s3,1,13,'')
exec('
declare '+@s1+'
'+@s2+'
exec('''+@s3+')
')
drop table #t
go--删除测试
drop table tdatatype,tdata,texpression
drop function f_sql/*--测试结果comid       biaoid      data         time    
----------- ----------- ------------ ------- 
1           1           .41          2004-05
1           2           -5.50        2004-05
2           1           .50          2004-05
2           2           1.17         2004-05
1           1           .51          2004-06
1           2           -.10         2004-06（所影响的行数为 6 行）
--*/

Answer 2

老大，厉害！
但我用的是Access，没法创建函数阿。
使用视图（多个也行）能实现吗？超强感谢！！！

Answer 3

ACESS我不熟,应该可以实现吧. ACCESS的是支持VBA自定义函数的,这点好像比SQL还强,不过速度肯定不高.

Answer 4

但我找不到创建函数的地方
至少运行create function无法成功。有知道的吗？麻烦告诉一声，谢了！

Answer 5

用ACCESS打开你的数据库,模块--新建

Answer 6

'ACCESS中的处理方法ACCESS打开你的数据库首先,添加一个辅助计算的表,表名为:a,表结构随便,保证有且只有一条记录就行了然后点"模块"--新建,将如下的代码粘贴过去,并保存,模块名任意:'--创建生成计算公式的函数
Public Function f_sql$(ByVal comid$, ByVal time$, expression$)
    Dim ire$, i&, j&, str1$
    
    ire = expression
    istr1 = "(select avg(data) from tdata where comid=" & comid & _
            " and [time]='" & time & "' and typeid="
    i = InStr(ire, "#")
    While i > 0
        j = val(Mid(ire, i + 1))
        If j <> 0 Then
            ire = Left(ire, i - 1) & istr1 & j & ")" & Mid(ire, i + Len(j & "a"))
            i = InStr(ire, "#")
        Else
            i = 1
        End If
    Wend
    f_sql$ = ire
End Function'得到公式计算结果的函数
Public Function f_calc(ByVal comid$, ByVal time$, expression$)
    Dim Re As DAO.Recordset
    
    Set Re = CurrentDb.OpenRecordset("select (" & f_sql(comid, time, expression) & ") from a")
    f_calc = Round(Re(0), 2)
End Function

Answer 7

'然后创建一个这样的视图,结果就出来了.SELECT a.comid, c.biaoid, f_calc(a.comid,a.[time],c.expression) AS data, a.[time]
FROM tdata AS a, tdatatype AS b, texpression AS c
WHERE a.typeid=b.id
GROUP BY a.comid, c.biaoid, a.[time], c.expression
ORDER BY a.[time], a.comid, c.biaoid;

Answer 8

大哥，不行啊，我已运行这个视图就提示我“用户定义类型为定义”，断点停在Public Function f_calc(ByVal companyid, ByVal operatetime, accountexpression)
    Dim Re As DAO.Recordset处。难道我有地方弄错了吗？
我定义了一个a表，该表就一个字段id，即一条记录id = 1大哥，再麻烦您帮我看一下是什么问题，谢谢！

Answer 9

工具--引用--找到并勾选"Microsoft DAO 3.6 Object Library"

Answer 10

790069233326790069233326790069233326790069233326790069233326C0VT125004223C0VT125004223790069233326790069233326790069233326C0VT125004223790069233326C0VT125004223C0VT125004223C0VT125004223C0VT125004223C0VT125004223

Answer 11

大哥，还是不行。这下变成运行错误了。
提示“标准表达式中数据类型不匹配”，发生错误的为这一行：Set Re = CurrentDb.OpenRecordset("select (" & f_sql(comid, time, expression) & ") from a")

Answer 12

检查你的公式有没有问题.出错时,
debug.print expression
debug.print "select (" & f_sql(comid, time, expression) & ") from a"看看结果是什么

Answer 13

公式没有问题
debug.print expression的结果为：
"#1/(#2+#4)"debug.print "select (" & f_sql(comid, time, expression) & ") from a"的结果为
select ((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=1)/((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=2) + (select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=4))) from a按理来说，这个结果应该是对的阿

Answer 14

搞不明白,在我的电脑上测试你的公式没错.或者你新建一个查询:
select ((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=1)/((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=2) + (select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=4))) from a看有没有错吧.

Answer 15

哦，我发现错误了！
得把比较时间的单引号换成#号（Access的要求）但现在的问题是，总共就那么很少的几条记录，但运行起来cpu就上100%，并且没有算完的趋势。大哥，麻烦您再帮我看看，超强感谢！

Answer 16

另外，单独执行查询：
select ((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=1)/((select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=2) + (select avg(data) from tdata where comid=1 and [time]='2004-5' and typeid=4))) from a是对的

Answer 17

把时间比较的单引号换成#就出事了,因为是通过#来生成查询语句的.而且你的time不是日期型的吧? 因为你的格式是 2004-5 

Answer 18

'如果真是日期的问题,你得改函数.'--创建生成计算公式的函数
Public Function f_sql$(ByVal comid$, ByVal time$, expression$)
    Dim ire$, i&, j&, str1$,ii&
    
    ire = expression
    istr1 = "(select avg(data) from tdata where comid=" & comid & _
            " and [time]=#" & time & "# and typeid="
    i = InStr(ire, "#")
    While i > 0
        j = val(Mid(ire, i + 1))
        If j <> 0 Then
            ire = Left(ire, i - 1) & istr1 & j & ")" 
ii = len(ire)
ire = ire & Mid(ire, i + Len(j & "a"))
            i = InStr(ire, "#",ii+1)
        Else
            i = 0
        End If
    Wend
    f_sql$ = ire
End Function

Answer 19

还不行你就把数据库发到:[email protected]

Answer 20

大哥，实在不好意思，还是不对。的确是时间的问题。我的time字段就是日期类型的，之所以写成2004-5是为了大家好理解。我使用您的新方法后，运行依然出错，出错位置为：
i = InStr(ire, "#",ii+1)
出错提示为：类型不匹配。我获取到此时的ire为((select avg(data) from tdata where comid=1 and [time]=#2004-5-1# and typeid=2)elect avg(data) from tdata  where comid=1 and [time]=#2004-5-1（未完）您解析的位置不正确，因为出现了“typeid=2)elect avg(data)”能不能麻烦您告诉我Instr(),val(),mid(),left()这些函数的参数的意义，这样我自己也好调。真的是太麻烦您了！

Answer 21

写惯了SQL的参数,改为i = InStr(ii+1,ire, "#")

Answer 22

大哥，我把库发给您了，麻烦您查收一下按照新方法改后，cpu依然100%，且无响应

Answer 23

Public Function f_sql$(ByVal comid$, ByVal time$, expression$)
    Dim ire$, i&, j&, str1$, ii&
    
    ire = expression
    istr1 = "(select avg(data) from PRIMARY_DATA where companyid=" & comid & _
            " and [operatetime]=#" & time & "# and datatypeid="
    i = InStr(ire, "#")
    ii = Len(istr1)
    While i > 0
        j = Val(Mid(ire, i + 1))
        If j <> 0 Then
            ire = Left(ire, i - 1) & istr1 & j & ")" & Mid(ire, i + Len(j & "a"))
            i = InStr(i + ii + Len(j & "a"), ire, "#")
        Else
            i = 0
        End If
    Wend
    f_sql$ = ire
End Function