MYSQL 巧用count，sum进行统计数据

SELECT a.user,count(b.order_id) as subcount,sum(if(b.verifysta='Y',1,0)) as passcount FROM vicidial_users a LEFT JOIN vicidial_order b on a.user = b.user WHERE a.user_group = 'TeamOne' GROUP BY a.user;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

sum(if(b.verifysta='Y',1,0))文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

如果verifysta为Y，就加1，否则为0，文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

这个太棒了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

把逻辑放到sql中，会大大的减少程序的工作量，而且效率很高。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

这样既能获取到全部的订单提交，也能获取审核通过的订单提交。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

select a.user,a.full_name,count(b.order_id) as subcount,sum(if(b.verifysta='Y',1,0)) as passcount from vicidial_users a LEFT JOIN (SELECT user,order_id,verifysta from vicidial_order where time>UNIX_TIMESTAMP('2015-11-7') and time<UNIX_TIMESTAMP('2015-11-7 23:59:59') and user_group = 'TeamOne')  b on a.user = b.user WHERE a.user_group = 'TeamOne' GROUP BY a.user;

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

根据日期筛选，先筛选出order数据（where条件都在其中），如果where放在外面的话，就会把数据过滤掉了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

拆分开来如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

SELECT user,order_id,verifysta from vicidial_order where time>UNIX_TIMESTAMP('2015-11-7') and time<UNIX_TIMESTAMP('2015-11-7 23:59:59') and user_group = 'TeamOne';

根据条件，获取所有的存在的数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

select a.user,a.full_name,b.order_id,b.verifysta='Y' from vicidial_users a LEFT JOIN (SELECT user,order_id,verifysta from vicidial_order where time>UNIX_TIMESTAMP('2015-11-7') and time<UNIX_TIMESTAMP('2015-11-7 23:59:59') and user_group = 'TeamOne')  b on a.user = b.user WHERE a.user_group = 'TeamOne';

获取全部的数据，不进行count、sum、group by 处理。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html

以上就是MYSQL 巧用count，sum进行统计数据的全部内容。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/20850.html