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

2021年1月17日08:27:08 发表评论 118 views

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;

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

如果verifysta为Y,就加1,否则为0,

这个太棒了。

把逻辑放到sql中,会大大的减少程序的工作量,而且效率很高。

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

这样既能获取到全部的订单提交,也能获取审核通过的订单提交。

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;

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

根据日期筛选,先筛选出order数据(where条件都在其中),如果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';

根据条件,获取所有的存在的数据。

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

 

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 处理。

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

以上就是MYSQL 巧用count,sum进行统计数据的全部内容。

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: