MySQL 查询本周，上周，按月查询，按年查询

1. 本周上周数据查询

 SELECT
      CASE
        datas.weeks
        WHEN 1 THEN
          '1'
        WHEN 2 THEN
          '2'
        WHEN 3 THEN
          '3'
        WHEN 4 THEN
          '4'
        WHEN 5 THEN
          '5'
        WHEN 6 THEN
          '6'
        WHEN 0 THEN
          '7'
        END AS weekday,
      IFNULL( datas.lastWeekMoney, 0 ) AS lastWeekMoney,
      IFNULL( datas.newWeekMoney, 0 ) AS newWeekMoney
    FROM
      (
        SELECT
          newDay.d AS weeks,
          lastWeek.totalUser AS lastWeekMoney,
          newWeek.totalUser AS newWeekMoney
        FROM (
               SELECT
                 0 AS `d` UNION ALL
               SELECT
                 1 AS `1` UNION ALL
               SELECT
                 2 AS `2` UNION ALL
               SELECT
                 3 AS `3` UNION ALL
               SELECT
                 4 AS `4` UNION ALL
               SELECT
                 5 AS `5` UNION ALL
               SELECT
                 6 AS `6`
             ) AS newDay
               LEFT JOIN (
          SELECT
            date_format( o.pay_time, '%w' ) AS `datetime`,
            sum( o.order_money ) AS `totalUser`
          FROM
            tb_rent_order o
          WHERE
          yearweek( date_format( o.pay_time, '%Y-%m-%d' ), 1 ) >= yearweek( now(), 1 )- 1
          AND yearweek( date_format( o.pay_time, '%Y-%m-%d' ), 1 ) < yearweek( now(), 1 )
    AND pay_time IS NOT NULL
         
          GROUP BY
            date_format( o.pay_time, '%w' )) AS lastWeek ON lastWeek.datetime = newDay.d
               LEFT JOIN (
          SELECT
            date_format( us.pay_time, '%w' ) AS `datetime`,
            sum( us.order_money ) AS `totalUser`
          FROM
            tb_rent_order us
          WHERE
              yearweek(( now() - INTERVAL 1 DAY ), 0 ) = yearweek(( us.`pay_time` - INTERVAL 1 DAY ), 0 )
            AND pay_time IS NOT NULL
		
          GROUP BY
            date_format( us.pay_time, '%w' )) newWeek ON newWeek.datetime = newDay.d
      ) datas
    ORDER BY weekday ASC

2. 指定月份查询

SELECT
        dateTime.d AS DAY,
        ifnull(o.money, 0 ) monthMealTotal 
FROM
        (
        SELECT
                ADDDATE( y.FIRST, x.d - 1 ) AS d 
        FROM
                (
                SELECT
                        1 AS d UNION ALL
                SELECT
                        2 UNION ALL
                SELECT
                        3 UNION ALL
                SELECT
                        4 UNION ALL
                SELECT
                        5 UNION ALL
                SELECT
                        6 UNION ALL
                SELECT
                        7 UNION ALL
                SELECT
                        8 UNION ALL
                SELECT
                        9 UNION ALL
                SELECT
                        10 UNION ALL
                SELECT
                        11 UNION ALL
                SELECT
                        12 UNION ALL
                SELECT
                        13 UNION ALL
                SELECT
                        14 UNION ALL
                SELECT
                        15 UNION ALL
                SELECT
                        16 UNION ALL
                SELECT
                        17 UNION ALL
                SELECT
                        18 UNION ALL
                SELECT
                        19 UNION ALL
                SELECT
                        20 UNION ALL
                SELECT
                        21 UNION ALL
                SELECT
                        22 UNION ALL
                SELECT
                        23 UNION ALL
                SELECT
                        24 UNION ALL
                SELECT
                        25 UNION ALL
                SELECT
                        26 UNION ALL
                SELECT
                        27 UNION ALL
                SELECT
                        28 UNION ALL
                SELECT
                        29 UNION ALL
                SELECT
                        30 UNION ALL
                SELECT
                        31 
                ) x,
                ( SELECT '2023-05-01' - INTERVAL DAY ( '2023-05-01' ) - 1 DAY AS FIRST, DAY ( LAST_DAY( '2023-05-01' )) AS last ) y 
        WHERE
                x.d <= y.last 
        ) AS dateTime
        LEFT JOIN (
        SELECT
                SUM( order_money ) money,
                DATE_FORMAT( pay_time, '%Y-%m-%d' ) time 
        FROM
                tb_rent_order 
        WHERE
                DATE_FORMAT( pay_time, '%Y-%m' )= '2023-05' 
                AND pay_time IS NOT NULL 
        GROUP BY
        time 
        ) AS o ON dateTime.d = o.time  

方式二可以使用下面的SQL,使用循环获取对应的天数，补充0

select 
        DATE_FORMAT( pay_time, '%Y-%m-%d' ) as dayTime, sum(order_money) as money
from tb_rent_order where DATE_FORMAT( pay_time, '%Y-%m' )='2023-05'
group by dayTime 
order by dayTime asc

3. 指定年份查询

SELECT
      a.MONTH month,
      ifnull( b.monthSum, 0 ) monthMealTotal,
      ifnull( c.quarterSum, 0 ) quarterMealTotal,
      ifnull( d.yearSum, 0 ) yearMealTotal 
    FROM
      (
        SELECT
          1 MONTH UNION ALL
        SELECT
          2 MONTH UNION ALL
        SELECT
          3 MONTH UNION ALL
        SELECT
          4 MONTH UNION ALL
        SELECT
          5 MONTH UNION ALL
        SELECT
          6 MONTH UNION ALL
        SELECT
          7 MONTH UNION ALL
        SELECT
          8 MONTH UNION ALL
        SELECT
          9 MONTH UNION ALL
        SELECT
          10 MONTH UNION ALL
        SELECT
          11 MONTH UNION ALL
        SELECT
          12 MONTH
      ) a
        LEFT JOIN (
        SELECT MONTH
          ( pay_time ) MONTH,
          SUM( order_money ) monthSum
        FROM
          incometa_stistics_view
        WHERE
          YEAR ( pay_time )= '2023'
          AND meal_type = '你的月度套餐id'
          AND pay_time IS NOT NULL
        GROUP BY
          date_format( pay_time, '%Y-%m' ),
          MONTH
      ) b ON a.MONTH = b.
        MONTH LEFT JOIN (
        SELECT MONTH
          ( pay_time ) MONTH,
          SUM( order_money ) quarterSum
        FROM
          incometa_stistics_view
        WHERE
          YEAR ( pay_time )=  '2023'
          AND meal_type = '你的季度套餐id'
          AND pay_time IS NOT NULL
        GROUP BY
          date_format( pay_time, '%Y-%m' ),
          MONTH
      ) c ON a.MONTH = c.
        MONTH LEFT JOIN (
        SELECT MONTH
          ( pay_time ) MONTH,
          SUM( order_money ) yearSum
        FROM
          incometa_stistics_view
        WHERE
          YEAR ( pay_time )=  '2023'
          AND meal_type = '你的年度套餐id'
          AND pay_time IS NOT NULL
        GROUP BY
          date_format( pay_time, '%Y-%m' ),
          MONTH
      ) d ON a.MONTH = d.MONTH
    ORDER BY
      month ASC