原语句：

select

sum(sale_price) as account_money,

count(distinct point_card_no) as use_count,

sum(add_point_value) as point_sum

from tb_rm_coupon_point

where

delete_flg='0' and add_point_datetime >='2019/05/13' and add_point_datetime < '2019/05/20'

 执行时间：时间为24.9秒

 

结果：

注：tb_rm_coupon_point 此表有295288条数据

 

此语句扫描了24032条数据 得到了结果 and 用了索引扫描

 

改进查询方法：

 语句：

SELECT

  SUM( CASE WHEN add_point_datetime >='2019/05/13' and add_point_datetime < '2019/05/20' THEN

  sale_price ELSE 0 END) as 'account_money',

   SUM( CASE WHEN add_point_datetime >='2019/05/13' and add_point_datetime < '2019/05/20' THEN

  add_point_value ELSE 0 END) as 'point_sum'，

count( CASE WHEN add_point_datetime >='2019/05/13' and add_point_datetime < '2019/05/20'

THEN  true ELSE null END) as 'use_count'

FROM  tb_rm_coupon_point where delete_flg='0'

执行时间为：10秒左右   相比快了15秒