MySQL行列转换与聚合函数

沐浴阳光中拥抱糖果的女孩

已于 2022-02-22 19:23:43 修改

阅读量613

点赞数

文章标签： mysql

于 2022-02-22 19:22:06 首次发布

本文链接：https://blog.csdn.net/qq_21442135/article/details/123075227

版权

一、数据结构
在这里插入图片描述
二、需要结果：

三、MySQL行列转换与聚合函数

SELECT IFNULL(ADMDIVNAME, 'TATOLSUM') AS ADMDIVNAME,
	SUM(IF(STAFF_MANAGE.ORGANTYPECODE ='SYDW', STAFFTOTAL, 0)) AS SYDW_BZ_RS,
	SUM(IF(STAFF_MANAGE.ORGANTYPECODE ='XZJG', STAFFTOTAL, 0)) AS XZJG_BZ_RS,
	SUM(IF(STAFF_MANAGE.ORGANTYPECODE ='SYDW', JOBTOTAL, 0)) AS SYDW_JOB_RS,
	SUM(IF(STAFF_MANAGE.ORGANTYPECODE ='XZJG', JOBTOTAL, 0)) AS XZJG_JOB_RS,
	COUNT(DISTINCT (CASE WHEN STAFF_MANAGE.ORGANTYPECODE ='SYDW' THEN JOBTOTAL END)) AS SYDW_NUM,
	COUNT(DISTINCT (CASE WHEN STAFF_MANAGE.ORGANTYPECODE ='XZJG' THEN JOBTOTAL END)) AS XZJG_NUM
FROM STAFF_MANAGE
GROUP BY ADMDIVNAME
WITH ROLLUP;