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;

四：查询结果如下图：

参考链接：http://www.zybang.com/question/1e678ffe5142f577186228f303a8477a.html