declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when a.wzmc='''+wzmc+''' then c.sl*c.jhj else 0 end) as ['+wzmc+']'
from
(select wzmc from wzdm where len(wzdm)=3) tset @sql='select b.mc as [单位/类别], '
+@sql
+',sum(c.sl*c.jhj)*0.05 as 管理费,sum(c.sl*c.jhj)*1.05 as 合计'
+'from wzdm a,dwdm b,lld c '
+'where len(a.wzdm)=3 and left(c.wzdm,3)=a.wzdm and left(c.lldw,3)=b.dm and c.flrq between ''2010-01-01'' and ''2010-01-31'' and c.scbz<>1 and c.lldlb in(11,21,12,22)'
+'group by b.mc'
--print @sqlexec (@sql)
select
@sql=isnull(@sql+',','')
+'sum(case when a.wzmc='''+wzmc+''' then c.sl*c.jhj else 0 end) as ['+wzmc+']'
from
(select wzmc from wzdm where len(wzdm)=3) tset @sql='select b.mc as [单位/类别], '
+@sql
+',sum(c.sl*c.jhj)*0.05 as 管理费,sum(c.sl*c.jhj)*1.05 as 合计'
+'from wzdm a,dwdm b,lld c '
+'where len(a.wzdm)=3 and left(c.wzdm,3)=a.wzdm and left(c.lldw,3)=b.dm and c.flrq between ''2010-01-01'' and ''2010-01-31'' and c.scbz<>1 and c.lldlb in(11,21,12,22)'
+'group by b.mc'
--print @sqlexec (@sql)
解决方案 »
- 一道数据库的笔试题,感觉有点难~~
- ORACLE触发器问题,可否触发多表?如下
- 链接sqlserver2000,向其插入数据问题
- 关于plsql 编译procedure的奇怪问题!!
- 求救:我用demo_proc.mk对pro*c代码编译出错
- 怎样用代码实现ORACLE客户端NET8服务连接的功能?
- 求教-如何查询一条记录中,不为空的字段的数目
- 解答问题,得到的不仅仅是积分!
- 不知道是什么原因!奔4+windows2000server+oracle10g就是安装不成功!
- 如何在网络上连接数据库?
- 关于热备 ,oracle 数据库备份,
- win7安装oracle10g完成后登录时输入密码和用户名后界面就关闭
另外,改造成Oracle的话,给你写个大概:
set serveroutput on --设置开关,为了下面使用dbms_output.put_line输出
declare
sql_text varchar2(8000) := '';--变量定义以及使用,不需要t-sql中的"@"
begin --拼串的时候用"||"连接,赋值用":="
--第一处拼串的地方
sql_text := 'select sum(case when a.wzmc = ' || wzmc || 'then c.sl*c.jhj else 0
end...'; --后面的你自己拼一下
--第二处拼串的地方
sql_text := sql_text || 'select b.mc as [单位/类别], ....
--打印拼完的串
dbms_output.put_line(sql_text);
--执行拼完的串
EXECUTE immediate sql_text into 变量1,变量2 ..;--看看你的select语句会选出几个值,分别与INTO后面的变量对应,其中,变量1,变量2需要在declare与begin之间定义
end;
sql_text := sql_text || 'select b.mc as [单位/类别], ....
这一行后面少了个引号。。
不过,没关系。有环境的话,试一下就行。
declare
sql_text varchar2(8000) := '';--变量定义以及使用,不需要t-sql中的"@"
begin --拼串的时候用"||"连接,赋值用":="
--第一处拼串的地方
sql_text := 'select sum(case when w.wzmc = ' || wzmc || 'then l.sl*l.jhj else 0
end'||wzmc|| 'from (select wzmc from scb_wzdm where length(wzdm)=3) '
sql_text := sql_text || 'select d.dwmc dw,sum(l.sl*l.jhj)*0.05 glf,sum(l.sl*l.jhj)*1.05 hj'
||'from scb_wzdm w,scb_dwdm d,scb_lld l '
||'where length(w.wzdm)=3 and substr(l.wzdm,1,3)=w.wzdm and substr(l.lldw,1,3)=d.dwdm and to_char(l.flrqm,'yyyy-mm-dd') between '2010-01-01' and '2010-01-31' and l.scbz<>1 and l.lldlb in(11,21,12,22)'
||'group by d.dwmc' --打印拼完的串
dbms_output.put_line(sql_text);
--执行拼完的串
EXECUTE immediate sql_text into dw,glf;--看看你的select语句会选出几个值,分别与INTO后面的变量对应,其中,变量1,变量2需要在declare与begin之间定义
end;
ORA-06550: line 8, column 3:
PLS-00103: Encountered the symbol "SQL_TEXT" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol ";" was substituted for "SQL_TEXT" to continue.
ORA-06550: line 10, column 116:
PLS-00103: Encountered the symbol "YYYY" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMU
ORA-06550: line 10, column 126:
PLS-00103: Encountered the symbol ") between " when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between |
ORA-06550: line 10, column 148:
PLS-00103: Encountered the symbol " and " when expecting one of the following: ) , * & | = - + < / > at in is mod
弄了这些代码出来,但是报错,请教兰兰,是什么原因.