关于mysql增加小计和总计行的处理讨论

为什么不查出来再另统计？

自己写了个取巧方式只能单月进行统计，不能一次统计整年的。

SELECT
      CASE        
          WHEN ( grouping ( t.date_type, t.statistics_date ) = 1 ) THEN  concat( t.date_type, '旬') 
          WHEN ( grouping ( t.date_type, t.statistics_date ) = 3 ) THEN '合计' 
          ELSE t.statistics_date 
      END AS 'statistics_date',
            SUM( one_bus ) AS one_bus,
            SUM( two_bus ) AS two_bus,
            SUM( three_bus ) AS three_bus,
            SUM( four_bus ) AS four_bus,
            SUM( one_truck ) AS one_truck,
            SUM( two_truck ) AS two_truck,
            SUM( three_truck ) AS three_truck,
            SUM( four_truck ) AS four_truck,
            SUM( five_truck ) AS five_truck,
            SUM( six_truck ) AS six_truck,
            SUM( free_car ) AS free_car,
            SUM( police_car ) AS police_car,
            SUM( sum_bus ) AS sum_bus,
            SUM( sum_truck ) AS sum_truck 
    FROM
        (            
        -- 新增一列分类上、中、下旬
        SELECT
            CASE
                WHEN RIGHT ( statistics_date, 2 ) in ('01', '02','03','04', '05', '06', '07', '08', '09', '10') THEN concat( DATE_FORMAT(statistics_date, '%Y-%m'), '-1上')
        WHEN RIGHT ( statistics_date, 2 ) in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20') THEN concat( DATE_FORMAT(statistics_date, '%Y-%m'), '-2中')
        WHEN RIGHT ( statistics_date, 2 ) in ('21', '22', '23', '24', '25', '26', '27', '28', '29', '30','31') THEN concat( DATE_FORMAT(statistics_date, '%Y-%m'), '-3下')
       ELSE '其他' END AS date_type,
                tb.*
                FROM station_ck_txl_year tb
        ) t
    GROUP BY
     t.date_type,t.statistics_date WITH ROLLUP

@China996 数据库新手，练习一下 MySQL 的 CTE, GROUPING, ROLLUP，可以按：全部、月份、上中下旬统计（偷个懒，没搞年份）

WITH RECURSIVE

  -- 生成 365 行数据：('2022-01-01', 10), ('2022-01-02', 10), ...
  orig_data(date, num) AS (
    SELECT '2022-01-01', 10
     UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY), num
      FROM orig_data
     WHERE date < '2022-12-31'
  ),

  -- 每行数据添加字段：年份、月份、上中下旬、天数
  table_with_aparted_date_appended(date, num, year, mon, period, day) AS (
    SELECT date, num, YEAR(date), DATE_FORMAT(date, '%m'),
           (DAY(date) + 9 - (DAY(date) > 30)) DIV 10, DAY(date)
      FROM orig_data
  )

SELECT CASE GROUPING(mon) << 2 | GROUPING(period) << 1 | GROUPING(day)
            WHEN 0 THEN date
            WHEN 1 THEN CONCAT(year, '-', mon, ' ', MID('上中下', period, 1), '旬')
            WHEN 3 THEN CONCAT(year, '-', mon, ' 月份')
            WHEN 7 THEN '本次查询合计'
       END AS date,
       SUM(num) AS total
  FROM table_with_aparted_date_appended
 GROUP BY mon, period, day WITH ROLLUP;

运行结果如下：