-- 增强group by的使用
-- 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*) AS SUM, AVG(sal), job
FROM emp
GROUP BY job
-- 显示雇员总数,以及获得补助的雇员数
-- 法1
SELECT COUNT(*) AS SUM, COUNT(comm) -- and comm is not null
FROM emp
-- 法2
SELECT COUNT(*) AS SUM, COUNT(IF(comm IS NULL, NULL, comm)) -- and comm is not null
FROM emp
-- 统计没有获得补助的
SELECT COUNT(*) AS SUM, COUNT(IF(comm IS NULL, 1, NULL)) -- 这里条件语句别写反了
FROM emp
-- 去重查询管理者人数
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- 显示工资最大差额
SELECT MAX(sal) - MIN(sal)
FROM emp;
标签:COUNT,--,SUM,分组,统计,加强,comm,SELECT,emp
From: https://www.cnblogs.com/pureayu/p/17122302.html