MYSQL巧妙地利用count和sum来统计数据

SELECT a.user,count(b.order_id) ascount,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;

sum(if(b.verifysta='Y',1,0))

if verifysta Y,只需添加 1，否则为 0，

这太棒了。

将逻辑放入SQL中，大大减少了程序工作量，效率很高。

这样您不仅可以获取所有提交的订单，还可以获取已批准的订单提交。

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;

要按日期过滤，请首先过滤订单数据（其中包含条件）。如果在where之外，则数据被过滤掉。

可以细分为：

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';

根据条件获取所有现有数据。

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';

您可以检索所有数据，无需进行计数、汇总或处理分组。

以上就是智能使用MYSQL统计数据计数和求和的全部内容。