SQL GROUP BY 與 HAVING 子句實戰範例：查詢薪資 ≥ 2000 且人數 > 1 的部門

表結構 DEPARTMENT

需求

找出「薪資不低於 2000 的員工人數超過 1 人」的部門編號。

SQL 語句

SELECT DEPARTMENT_ID, COUNT(SALARY) AS emp_cnt
FROM DEPARTMENT
WHERE SALARY > 2000
GROUP BY DEPARTMENT_ID
HAVING COUNT(SALARY) > 1;

關鍵點解析

• WHERE SALARY > 2000 先過濾掉薪資 < 2000 的列。
• GROUP BY DEPARTMENT_ID 依部門分組。
• HAVING COUNT(SALARY) > 1 保留群組計數 > 1 的部門。

延伸練習

-- 顯示部門名、符合條件人數、最高薪資
SELECT d.DEPARTMENT_NAME, COUNT(e.SALARY) AS cnt, MAX(e.SALARY) AS max_sal
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.SALARY >= 2000
GROUP BY d.DEPARTMENT_NAME
HAVING COUNT(e.SALARY) > 1
ORDER BY cnt DESC;