MySQL汇总查询生成汇总行

最新推荐文章于 2023-07-20 15:54:05 发布

繁梦溪

最新推荐文章于 2023-07-20 15:54:05 发布

阅读量1.3k

点赞数 1

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/FG24151110876/article/details/117664132

版权

SQL 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

一、普通计数查询

SELECT
`me_province` AS `省份`,
COUNT(DISTINCT IF(`me_grade` REGEXP '三级',`me_name`,NULL)) AS `三级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '二级',`me_name`,NULL)) AS `二级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '一级',`me_name`,NULL)) AS `一级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '未定级',`me_name`,NULL)) AS `未定级医院数量`
FROM
`be_hmap`
GROUP BY
`me_province`

二、汇总行（WITH ROLLUP汇总数据）

SELECT 
`me_province` AS `省份`,
COUNT(DISTINCT IF(`me_grade` REGEXP '三级',`me_name`,NULL)) AS `三级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '二级',`me_name`,NULL)) AS `二级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '一级',`me_name`,NULL)) AS `一级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '未定级',`me_name`,NULL)) AS `未定级医院数量`
FROM
`be_hmap`
GROUP BY
`me_province`
WITH ROLLUP
;

三、汇总行(WITH ROLLUP；COALESCE函数处理为NULL显示“汇总”)

SELECT 
COALESCE(`me_province`,'总计') AS `省份`,
COUNT(DISTINCT IF(`me_grade` REGEXP '三级',`me_name`,NULL)) AS `三级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '二级',`me_name`,NULL)) AS `二级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '一级',`me_name`,NULL)) AS `一级医院数量`,
COUNT(DISTINCT IF(`me_grade` REGEXP '未定级',`me_name`,NULL)) AS `未定级医院数量`
FROM
`be_hmap`
GROUP BY
`me_province`
WITH ROLLUP
;