＋设为首页 ＋添加到收藏夹
update #SP_GL_BOOK_T
set DR_CR = case when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from #SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) > 0 then 借
when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from #SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) < 0 then 贷
when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from #SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) = 0 then 平
end
from #SP_GL_BOOK_T a
where PERD_ORDER = 1
上面是个MS SQL语句，
字段
REC_INDEX int IDENTITY (1, 1) not null
如何该写成ORACLE 语句？多谢！
update SP_GL_BOOK_T a
set DR_CR =(select decode(sign(sum(D_STAD_AMT)-sum(C_STAD_AMT)),1,借,-1,贷,0,平) from SP_GL_BOOK_T where a.REC_INDEX <= REC_INDEX and PERD_ORDER < 2) where a.PERD_ORDER = 1;
不需要做什么特别的修改，oracle也有case语句，而且与sql server是一样的，只需把表名的#去掉就行了，如下：
update SP_GL_BOOK_T
set DR_CR = case when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) > 0 then 借
when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) < 0 then 贷
when (select sum(D_STAD_AMT)-sum(C_STAD_AMT) from SP_GL_BOOK_T where REC_INDEX <= a.REC_INDEX and PERD_ORDER < 2) = 0 then 平
end
FROM SP_GL_BOOK_T a
WHERE PERD_ORDER = 1
is
sum_total float;
begin
select sum(D_STAD_AMT)-sum(C_STAD_AMT) into sum_total from SP_GL_BOOK_T,SP_GL_BOOK_T a
where REC_INDEX <= a.REC_INDEX
and PERD_ORDER < 2;
if sum_total>0 then
update SP_GL_BOOK_T set DR_CR=借 where PERD_ORDER = 1;
end if;
if sum_total<0 then
update SP_GL_BOOK_T set DR_CR=贷 where PERD_ORDER = 1;
end if;
if sum_total=0 then
update SP_GL_BOOK_T set DR_CR=平 where PERD_ORDER = 1;
end if;
END;
