数据库中添加月份查询以及统计
WITH table_a AS (
SELECT
ROW_NUMBER() OVER (ORDER BY city DESC) AS 序号,
to_char(over_time,'YYYY-MM') AS 归档时间月份,
a.*
FROM diffic_install a
WHERE $[over_time >= to_date('${开始日期}', 'YYYY-MM-DD')]
AND $[over_time <= to_date('${结束日期}', 'YYYY-MM-DD')]
),
table_tj AS (
SELECT 归档时间月份,city AS 地市, COUNT(*) AS 总数, to_char(trunc(SYSDATE),'YYYY-MM-DD') AS 统计时间 FROM table_a GROUP BY 归档时间月份,city
)
SELECT *
FROM (
SELECT a.*,dense_rank() over(order by a.总数,a.归档时间月份 desc) AS 排名 FROM table_tj a
UNION ALL
SELECT NULL,'全区' AS 地市, SUM(总数) AS 总数,NULL, NULL AS 统计时间 FROM table_tj
) main
ORDER BY
CASE main.地市
WHEN '宁' THEN 1
WHEN '林' THEN 2
WHEN '州' THEN 3
WHEN '林' THEN 4
WHEN '色' THEN 5
WHEN '池' THEN 6
WHEN '港' THEN 7
WHEN '钦' THEN 8
WHEN '梧' THEN 9
WHEN '北' THEN 10
WHEN '崇' THEN 11
WHEN '来' THEN 12
WHEN '贺' THEN 13
WHEN '防' THEN 14
WHEN '全区' THEN 15
END;