mysql获得汇总值
mysql获得汇总值
提问于 2017-03-29 01:11:01
我想问一下只有汇总值的mysql，这是我的代码
SELECT tbldept.CodeDept AS Dept,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
CONCAT(ROUND(20), '%') AS TargetOT,
round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
FROM tbldept
LEFT JOIN tblemp
ON tbldept.CodeDept = tblemp.CodeDept AND
tblemp.class = "DIR"
WHERE tbldept.CodeDept != 'FIN' AND
tbldept.CodeDept != 'HRT' AND
tbldept.CodeDept != 'ISD'
GROUP BY tbldept.CodeDept with ROLLUP;汇总查询的结果：
我如何才能得到汇总值？谢谢你之前
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-03-29 02:11:50
如果您只想要rollup行，只需使用having
SELECT tbldept.CodeDept AS Dept,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
CONCAT(ROUND(20), '%') AS TargetOT,
round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
FROM tbldept
LEFT JOIN tblemp
ON tbldept.CodeDept = tblemp.CodeDept AND
tblemp.class = "DIR"
WHERE tbldept.CodeDept != 'FIN' AND
tbldept.CodeDept != 'HRT' AND
tbldept.CodeDept != 'ISD'
GROUP BY tbldept.CodeDept with ROLLUP
HAVING Dept IS NULL;
编辑：
having应该有效，检查是否存在其他原因。
或者，只需用子查询包装查询：
SELECT *
FROM (
SELECT tbldept.CodeDept AS Dept,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
CONCAT(ROUND(20), '%') AS TargetOT,
round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
FROM tbldept
LEFT JOIN tblemp
ON tbldept.CodeDept = tblemp.CodeDept AND
tblemp.class = "DIR"
WHERE tbldept.CodeDept != 'FIN' AND
tbldept.CodeDept != 'HRT' AND
tbldept.CodeDept != 'ISD'
GROUP BY tbldept.CodeDept with ROLLUP
) t
WHERE Dept IS NULL;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43082552
复制相关文章
相似问题
腾讯云开发者
