SQL HAVING 子句 - SQL 教學

摘要：在本教程中，您將學習如何使用 SQL HAVING 子句根據條件篩選分組。

SQL HAVING 子句簡介 #

GROUP BY 子句將結果集的行分組。要為篩選分組指定條件，您需要使用 HAVING 子句。

如果您在沒有 GROUP BY 子句的情況下使用 HAVING 子句，HAVING 子句的行為將類似於 WHERE 子句。

以下是 HAVING 子句的語法

SELECT
  column1,
  column2,
  aggregate_function (column3)
FROM
  table1
GROUP BY
  column1,
  column2
HAVING
  group_condition;Code language: SQL (Structured Query Language) (sql)

請注意，HAVING 子句緊跟在 GROUP BY 子句之後。

SQL HAVING 子句示例 #

我們將使用示例資料庫中的以下 employees 和 departments 表進行演示。

要獲取經理及其直接下屬，您可以使用 GROUP BY 子句按經理對員工進行分組，並使用 COUNT 函式來統計直接下屬的數量。

SELECT
  manager_id,
  COUNT(employee_id) direct_reports
FROM
  employees
WHERE
  manager_id IS NOT NULL
GROUP BY
  manager_id
ORDER BY
  direct_reports;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

 manager_id | direct_reports
------------+----------------
        201 |              1
        102 |              1
        120 |              1
        205 |              1
        123 |              2
        103 |              4
        101 |              5
        114 |              5
        108 |              5
        100 |             14

要查詢至少有五個直接下屬的經理，您可以在上述查詢中新增一個 HAVING 子句，如下所示：

SELECT
  manager_id,
  COUNT(employee_id) direct_reports
FROM
  employees
WHERE
  manager_id IS NOT NULL
GROUP BY
  manager_id
HAVING
  COUNT(employee_id) >= 5
ORDER BY
  direct_reports;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

 manager_id | direct_reports
------------+----------------
        101 |              5
        114 |              5
        108 |              5
        100 |             14

SQL HAVING 與 SUM 函式示例 #

以下語句計算公司為每個部門支付的薪資總和，並僅選擇薪資總和在 20000 到 30000 之間的部門。

SELECT
  department_id,
  SUM(salary) total_salary
FROM
  employees
GROUP BY
  department_id
HAVING
  SUM(salary) BETWEEN 20000 AND 30000
ORDER BY
  total_salary;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

 department_id | total_salary
---------------+--------------
            11 |     20300.00
             3 |     24900.00
             6 |     28800.00

SQL HAVING 與 MIN 函式示例 #

要查詢員工最低薪資大於 10000 的部門，您可以使用以下查詢：

SELECT
  department_name,
  MIN(salary) min_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  MIN(salary) >= 10000
ORDER BY
  MIN(salary);Code language: SQL (Structured Query Language) (sql)

試一試

輸出

 department_name  | min_salary
------------------+------------
 Public Relations |   10000.00
 Executive        |   17000.00Code language: PHP (php)

查詢工作原理：

首先，使用 GROUP BY 子句按部門對員工進行分組。
其次，使用 MIN 函式查詢每個分組的最低薪資。
第三，將條件應用於 HAVING 子句。

SQL HAVING 子句與 AVG 函式示例 #

要查詢員工平均薪資在 5,000 到 7,000 之間的部門，您可以使用 AVG 函式，查詢如下：

SELECT
  department_name,
  ROUND(AVG(salary), 2) average_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  AVG(salary) BETWEEN 5000 AND 7000
ORDER BY
  average_salary;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

 department_name | average_salary
-----------------+----------------
 IT              |        5760.00
 Shipping        |        5885.71
 Human Resources |        6500.00

HAVING 與 WHERE 的對比 #

WHERE 子句在行被 GROUP BY 子句彙總為分組之前對行應用條件。而 HAVING 子句在行被分組後對分組應用條件。

因此，需要重點注意的是，HAVING 子句在 GROUP BY 子句之後應用，而 WHERE 子句在 GROUP BY 子句之前應用。

SQL HAVING

SQL HAVING 子句簡介 #

SQL HAVING 子句示例 #

SQL HAVING 與 SUM 函式示例 #

SQL HAVING 與 MIN 函式示例 #

SQL HAVING 子句與 AVG 函式示例 #

HAVING 與 WHERE 的對比 #

摘要 #

資料庫 #

測驗 #