select (
case (select count(1) from BY_TASK_WORKITEM where task_id='34424141')
when 0 then '没有完成的记录'
else
(select count(1) from BY_TASK_WORKITEM where task_id='34424141' and completion='已完成')/
(select count(1) from BY_TASK_WORKITEM where task_id='34424141' ) *100 ||''
end
) as donetotal from dual
create or replace function getTotal(taskId in number) return varchar2 is
num1 number;
num2 number;
resultStr varchar2(128);
begin
select count(1) into num1 from BY_TASK_WORKITEM where task_id=taskId;
select count(1) into num2 from BY_TASK_WORKITEM where task_id=taskId and completion='已完成';
if (num1=0 or num2 =0) then
resultStr:='没有完成的记录';
else
resultStr:=(num2/num1)*100||'%';
end if;
return resultStr;
end;
-- 调用
select (gettotal(26129857))from dual