前端需求:
表结构:
通过前端給过来的图,我们只需要统计一周内的订单总量和 订单总金额,一开始我的想法是写多个sql语句,然后再插入到一个数组中去,最后的结果你知道的,效率很低很低。
为了能够卷赢其他成员,我只好努力百度百度。
最后实现的SQL:不用烦,我当初第一眼和你看这个一模一样的,一度感觉自己从来没有学习过mysql。😂
- select a.clickDate,ifnull(b.salesSum,0) as salesSum,ifnull(b.orderCount,0) as orderCount
- from (
- SELECT curdate() as clickDate
- union all
- SELECT date_sub(curdate(), interval 1 day) as clickDate
- union all
- SELECT date_sub(curdate(), interval 2 day) as clickDate
- union all
- SELECT date_sub(curdate(), interval 3 day) as clickDate
- union all
- SELECT date_sub(curdate(), interval 4 day) as clickDate
- union all
- SELECT date_sub(curdate(), interval 5 day) as clickDate
- union all
- SELECT date_sub(curdate(), interval 6 day) as clickDate
- ) a left join (
- select date(payment_time) as datetime, sum(payment_price) as salesSum ,count(id) as orderCount
- from t_order
- where state in (1,2,3)
- group by date(payment_time)
- ) b on a.clickDate = b.datetime;
下面给大家解释一下哈哦。