SELECT IFNULL(SUM(p.settle_price),0)AS settlePrice,'本月'AS typeName
FROM package p
WHERE DATE_FORMAT( p.consume_time,'%Y%m')= DATE_FORMAT( CURDATE(),'%Y%m')UNIONSELECT IFNULL(SUM(p.settle_price),0)AS settlePrice,'上月'AS typeName
FROM package p
WHERE PERIOD_DIFF( DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT( p.consume_time,'%Y%m'))=1UNIONSELECT IFNULL(SUM(p.settle_price),0)AS settlePrice,'上上月'AS typeName
FROM package p
WHERE PERIOD_DIFF( DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT( p.consume_time,'%Y%m'))=2
二. 按照 日 周 月 年 的周期进行查询统计
<!-- 按日查询 --> SELECT
DATE_FORMAT(created_date,'%Y-%m-%d')astime,sum(money) money
FROM shpping_order where shop_id =1000GROUPBYtime<!-- 按月查询 --> SELECT
DATE_FORMAT(created_date,'%Y-%m')astime,sum(money) money
FROM shpping_order where shop_id =1000GROUPBYtime<!-- 按年查询 --> SELECT
DATE_FORMAT(created_date,'%Y')astime,sum(money) money
FROM shpping_order where shop_id =1000GROUPBYtime<!-- 按周查询 --> SELECT
DATE_FORMAT(created_date,'%Y-%u')astime,sum(money) money
FROM shpping_order where shop_id =1000GROUPBYtime
一. 用于统计当月,上月,上上月的销售额统计。通过格式化销售日期进行查询。SELECT IFNULL(SUM(p.settle_price),0) AS settlePrice,'本月' AS typeName FROM package p WHERE DATE_FORMAT( p.consume_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )UNIONSELECT IFNULL(SUM(p.settle_price),0) A