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


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

讨论数量: 3

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

2周前 评论
China996 (楼主) 2周前

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

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
2周前 评论

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