讨论数量:
自己写了个取巧方式只能单月进行统计,不能一次统计整年的。
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;
运行结果如下:
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 |
推荐文章: