前端需求:
表结构:
通过前端給过来的图,我们只需要统计一周内的订单总量和 订单总金额,一开始我的想法是写多个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;
下面给大家解释一下哈哦。