UC知道ms sql到oracle sql的转换
来源：百度知道 编辑：UC知道 时间：2024/05/26 04:14:13
请大家帮帮忙，把下面的sql语句转换成能在oracle下面跑的，此语句在ms sql server下编译无误，请大家帮个忙，急，在线等，万分感谢！
declare @sql varchar(8000)
set @sql = 'select (case when grouping(t2.itemname)=1 then ''总计'' else t2.itemname end) as itemname,'
select @sql = @sql+ ' sum(case when TRANSACTID= '+TRANSACTID+' then 1 else 0 end) as ['+TRANSACTID+'],'
From (Select Distinct TRANSACTID From C_ACCEPT_STATUS) T
set @sql = left(@sql,len(@sql)-1)+',(case when count(1)=0 then ''0'' else count(*) end) as ''总计''
from WORKAPPLY t left join C_ADMIN_ITEM t2
on t.itemid=t2.itemcode group by t2.itemname with rollup'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select (case when grouping(t2.itemname)=1 then ''总计'' else t2.itemname end) as itemname,'
select @sql = @sql+ ' sum(case when TRANSACTID= '+TRANSACTID+' then 1 else 0 end) as ['+TRANSACTID+'],'
From (Select Distinct TRANSACTID From C_ACCEPT_STATUS) T
set @sql = left(@sql,len(@sql)-1)+',(case when count(1)=0 then ''0'' else count(*) end) as ''总计''
from WORKAPPLY t left join C_ADMIN_ITEM t2
on t.itemid=t2.itemcode group by t2.itemname with rollup'
exec(@sql)
1、定义一个游标变量
var p_cursor refcursor;
2、修改后的pl/sql
DECLARE
STR_SQL1 VARCHAR(8000) :=' ';
STR_SQL2 VARCHAR(8000) :=' ';
BEGIN
STR_SQL1 := 'select (case when grouping(t2.itemname)=1 then ''总计'' else t2.itemname end) as itemname,';
FOR rec IN (Select Distinct TRANSACTID From C_ACCEPT_STATUS)
LOOP
STR_SQL2 := STR_SQL2||' sum(case when TRANSACTID= '''||rec.TRANSACTID||''' then 1 else 0 end) as "'|| rec.TRANSACTID ||'",';
END LOOP;
STR_SQL2 := STR_SQL1 ||STR_SQL2||'(case when count(1)=0 then ''0'' else count(*) end) as "总计" from WORKAPPLY t left join C_ADMIN_ITEM t2 on t.itemid=t2.itemcode group by rollup(t2.itemname) ';
OPEN :p_cursor FOR STR_SQL2;
END;
/
3、打印查询结果
print p_cursor;
---
以上，希望对你有所帮助。
declare ls_s