mysql根据统计值排序

公司有个需求，需要对用户的购买数和分销用户数进行排序，而现在的统计是根据查询遍历出来的，首先想到的是用脚本实现，但是考虑到用户表的数据量25万 就放弃了，于是想试试mysql能不能完成这样的工作，下面就是写出来的sql语句，大家可以参考一下

SELECT `id`,`create_time`,(SELECT count(id) FROM my_order AS o WHERE `user_id` = u.id AND `status` = 3 AND `type` = 0 LIMIT 0,1) as buy,(SELECT count(id) FROM my_divid_order AS d WHERE `user_id` = u.id AND `status` <> 0 LIMIT 0,1) as divide FROM passport AS u ORDER BY `buy` DESC LIMIT 0,20

美化一下

SELECT
	`id`,
	`create_time`,
	(SELECT count( id ) FROM my_order AS o WHERE `user_id` = u.id  AND `status` = 3 AND `type` = 0 LIMIT 0,1 ) AS buy,
	(SELECT count( id ) FROM my_divid_order AS d WHERE `user_id` = u.id AND `status` <> 0 LIMIT 0,1 ) AS divide 
FROM
	passport AS u 
ORDER BY
	`buy` DESC 
	LIMIT 0,
	20

 

转载于:https://my.oschina.net/colinadmin/blog/3102719