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


想统计一个月上中下旬,类似于增加小计、总计行的的数据。
mysql中有什么合适函数可以进行解决呢
GROUPING函数的使用方式

讨论数量: 7

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

1年前 评论
China996 (楼主) 1年前

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

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
1年前 评论

@China996 数据库新手,练习一下 MySQLCTE, 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;

运行结果如下:

date total
2022-01-01 10
2022-01-02 10
…… ……
2022-01-10 10
2022-01 上旬 100
2022-01-11 10
…… ……
2022-01-31 10
2022-01 下旬 110
2022-01 合计 310
…… ……
2022-12 下旬 110
2022-12 合计 310
本次查询合计 3650
1年前 评论
wxf666 (作者) 1年前
China996 (楼主) 1年前
wxf666 (作者) 1年前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!