利用聚合函数GROUP BY ROLLUP 添加一行合计数据
select case when qugroupc=0 and mgroupc=1 then CONCAT(substring( REVERSE(qu),1,1),'','季度') when qugroupc=1 and mgroupc=1 then '年度' else m end m,count from ( select CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE)) qu,DATE_FORMAT(DIAG_DATE,'%Y%m') m,count(1) count, grouping(CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))) qugroupc,grouping(DATE_FORMAT(DIAG_DATE,'%Y%m'))mgroupc from s_chronic WHERE DIAG_DATE BETWEEN '2021-01-01' AND now() group by qu,m WITH ROLLUP ) as t
效果图:
子查询
select CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE)) qu,DATE_FORMAT(DIAG_DATE,'%Y%m') m,count(1) count, grouping(CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))) qugroupc,grouping(DATE_FORMAT(DIAG_DATE,'%Y%m'))mgroupc from s_chronic WHERE DIAG_DATE BETWEEN '2021-01-01' AND now() group by qu,m WITH ROLLUP
的效果图: