问题
在开发的时候,遇到一个相对复杂的逻辑,需要先用CASE...WHEN查询数据,再用SUM求和,首先
把我错误的写法,放在下面,供大家参考:(PS: 羞羞羞...)
SELECT
a.deptName,
b.problemNums,
b.problemNums AS problemRectNums,
c.dangerNums,
c.dangerNums AS dangerDealNums,
c.dangerRectNums
FROM
( SELECT DEPT_NAME deptName FROM statistics_city t GROUP BY t.DEPT_NAME ) a
LEFT JOIN (
SELECT
DEPT_NAME,
IFNULL( count( 1 ), 0 ) problemNums
FROM
statistics_city t,
inspect_problem pro
WHERE
pro.REMOVED = 0
AND t.DEPT_ID = pro.USER_DEPT_ID
GROUP BY
t.DEPT_NAME
) b ON a.deptName = b.DEPT_NAME
LEFT JOIN (
SELECT
DEPT_NAME,
IFNULL( count( 1 ), 0 ) dangerNums,
SUM(CASE danger.IS_RECTIFICATION
WHEN 1 THEN
**COUNT(1)** ELSE 0
END) AS dangerRectNums
FROM
statistics_city t,
inspect_danger danger
WHERE
danger.REMOVED = 0
AND t.DEPT_ID = danger.USER_DEPT_ID
GROUP BY
t.DEPT_NAME
) c ON a.deptName = c.DEPT_NAME
错误提示
解析
Invalid use of group function
错误内容是: 集函数的无效用法,多个函数用法冲突
因为不能对包含聚合或子查询的表达式执行聚合函数,SUM内部不能嵌套COUNT方法,
解决办法
将COUNT(1)更改成1,即可
完成SQL如下:
SELECT
a.deptName,
b.problemNums,
b.problemNums AS problemRectNums,
c.dangerNums,
c.dangerNums AS dangerDealNums,
c.dangerRectNums
FROM
( SELECT DEPT_NAME deptName FROM statistics_city t GROUP BY t.DEPT_NAME ) a
LEFT JOIN (
SELECT
DEPT_NAME,
IFNULL( count( 1 ), 0 ) problemNums
FROM
statistics_city t,
inspect_problem pro
WHERE
pro.REMOVED = 0
AND t.DEPT_ID = pro.USER_DEPT_ID
GROUP BY
t.DEPT_NAME
) b ON a.deptName = b.DEPT_NAME
LEFT JOIN (
SELECT
DEPT_NAME,
IFNULL( count( 1 ), 0 ) dangerNums,
SUM(CASE danger.IS_RECTIFICATION
WHEN 1 THEN
1 ELSE 0
END) AS dangerRectNums
FROM
statistics_city t,
inspect_danger danger
WHERE
danger.REMOVED = 0
AND t.DEPT_ID = danger.USER_DEPT_ID
GROUP BY
t.DEPT_NAME
) c ON a.deptName = c.DEPT_NAME