--百分比正确显示的函数--
CREATE FUNCTION orafun.TO_CHAR (num decimal(31,2)) RETURNS VARCHAR(60) LANGUAGE
SQL CONTAINS SQL SPECIFIC TOCHARDECIMAL1 NO EXTERNAL ACTION DETERMINISTIC
RETURN case when num <0 and ( length(char(num))-locate('.',char(num))) >1
then '-' | |varchar(rtrim(char(bigint(num*(-1)))) | |substr( char(num*(-1)),locate('.',char(num*(-1))),length(char(num*(-1)))-locate('.',char(num*(-1)))))
when ( length(char(num))-locate('.',char(num))) >1 then varchar(rtrim(char(bigint(num))) | |substr(
char(num),locate('.',char(num)),length(char(num))-locate('.',char(num))))
else varchar(char(bigint(num))) end;
-- 用函数
select orafun.TO_CHAR(1*1.0*100/50)||'%' from CM_CUST_INFO_MODIFY_QUALITY cq,CM_CARD_SIGN_ADD_QUALITY csq
group by cq.OPERATOR_ID
-- 在ireport中可以加上上'%',SQL中不加'%'因为cast会影响效率.
select distinct cq.OPERATOR_ID,
(select count(*) from CM_CUST_INFO_MODIFY_QUALITY ) as CUST_QUA_NUM,
cast(
(select count(*) from CM_CUST_INFO_MODIFY_QUALITY )*1.0*100/(select count(*) from CM_CUST_INFO_MODIFY_ENTITY ce ,CM_CUST_INFO_MODIFY cm where ce.CUST_MODIFY_ID = cm.ID )
as decimal(4,2)
)
as CUST_QUA_RATE,
(select count(*) from CM_CARD_SIGN_ADD_QUALITY ) as CARD_QUA_NUM,
cast(
(select count(*) from CM_CARD_SIGN_ADD_QUALITY )*1.0*100/(select count(*) from CM_CARD_SIGN_ADD ca )
as decimal(4,2)
)
as CARD_QUA_RATE
from CM_CUST_INFO_MODIFY_QUALITY cq
group by cq.OPERATOR_ID
--一种不太好的写法,可能会带前导00
select distinct
rtrim(
cast
(
cast
(
(select count(*) from CM_CUST_INFO_MODIFY_QUALITY )*1.0*100/(select count(*) from CM_CUST_INFO_MODIFY_ENTITY ce ,CM_CUST_INFO_MODIFY cm where ce.CUST_MODIFY_ID = cm.ID )
as decimal(5,3)
)
as char(50)
)
)
||'%'
as CUST_QUA_RATE
from CM_CUST_INFO_MODIFY_QUALITY cq,CM_CARD_SIGN_ADD_QUALITY csq
group by cq.OPERATOR_ID