百分比正确显示的函数

--百分比正确显示的函数--

 

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中可以加上上'%'，ＳＱＬ中不加＇％＇因为ｃａｓｔ会影响效率．

 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

 

 

－－一种不太好的写法，可能会带前导００

 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