MySQL中百分数生成和排序

问题描述

最近在开发APP的一个报表后台，使用的是MySQL数据库，遇到一个很郁闷的问题就是MySQL对百分比排序的处理，之前的报表都是在Oracle当中，从来没纠结过百分比怎么处理。在MySql中百分比直接排序效果如下；

100% 90% 9% 80% 8% 70%

这个坑爹问题已经很明显了，下面说下怎么处理

解决过程

首先看一下‘100%’怎么生成：

CONCAT(ROUND(num1*100/num2,2),'%')

这里的意思就是：

ROUND(num1*100/num2,2)结果保留2位有效数字

CONCAT(num,'%')在num后面加上‘%’

CASE-WHEN处理：num2不能为0

CASE   WHEN num2=0THENCONCAT(0.00,'%')   ELSEnum = num1/num2 ENDASNum

除数、被除数都‘+1’处理

num = (num1+1)/(num2+1)当然这里是指数据很庞大，如果你的数据很小的话，也可以+0.01，这个视情况而定。

SQL案例

SELECT   ad.PROVINCE_ID,   ad.PROVINCE_NAME,   SUM(ad.SEND_NUM) AS SEND_NUM,   SUM(ad.CONFIRM_SIGN_NUM) AS SIGN_NUM,   SUM(ad.POS_SIGN_NUM) AS POS_SIGN_NUM,   SUM(ad.APP_SIGN_NUM) AS APP_SIGN_NUM,   CASE     WHEN (       SUM(ad.CONFIRM_SIGN_NUM) + SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     ) = 0     THENCONCAT(0.00, '%')     ELSECONCAT(       ROUND(         (           SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)         ) * 100 / (           SUM(ad.CONFIRM_SIGN_NUM) + SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)         ),         2       ),       '%'     )   ENDAS ACT_SIGN_RET FROM   app_sign_data ad WHERE ad.CREATE_DATE > '2016-7-28'   AND ad.CREATE_DATE < '2016-8-1' GROUPBY ad.PROVINCE_NAME ORDERBY   CASE     WHEN (       SUM(ad.CONFIRM_SIGN_NUM) + SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     ) = 0     THEN (       SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     ) * 100 / (       SUM(ad.CONFIRM_SIGN_NUM) + SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     ) = 0     ELSE (       SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     ) * 100 / (       SUM(ad.CONFIRM_SIGN_NUM) + SUM(ad.POS_SIGN_NUM) + SUM(ad.APP_SIGN_NUM)     )   ENDDESC   /*(SUM(ad.POS_SIGN_NUM)+SUM(ad.APP_SIGN_NUM)+0.01)*100/   (SUM(ad.CONFIRM_SIGN_NUM)+SUM(ad.POS_SIGN_NUM)+SUM(ad.APP_SIGN_NUM)+0.01) DESC*/