mysql两列结果相除_mysql 通过子查询实现两列相除

摘要：催收系统中有个根据前一天催收回单率排序的sql，其中：前一天催收回单率=前一天催收回单量/前一天的全部回单量，下面是对应的SQL语句：

一：查询前一天的回单量：

SELECT

count(1) AS '前一天回单量'

FROM

cui_task t

WHERE

DATE_FORMAT(t.assignDate, '%Y-%m-%d') = date_sub(curdate(), INTERVAL 1 DAY)

AND t.`status` = 490

AND t.assignUserId = s.userId

二：查询前一天的全部单量：

SELECT

count(1) AS '全部单量'

FROM

cui_task t

WHERE

t.assignUserId = s.userId

三：查询前一天的回单量：

SELECT

s.id,

s.type,

s.userId,

s.sort,

s.cuiGroup,

u. NAME,

u.mobile,

(

SELECT

count(1) AS '前一天回单量'

FROM

cui_task t

WHERE

DATE_FORMAT(t.assignDate, '%Y-%m-%d') = date_sub(curdate(), INTERVAL 1 DAY)

AND t.`status` = 490

AND t.assignUserId = s.userId

) / (

SELECT

count(1) AS '全部单量'

FROM

cui_task t

WHERE

t.assignUserId = s.userId

) AS rate

FROM

cui_staff s

LEFT JOIN sudaibear.uc_users u ON u.id = s.userId

WHERE

s. STATUS = 1

AND s.cuiGroup = 1

ORDER BY

rate DESC;

四：查询结果如下图：