MySQL之使用WITH子句和臨時(shí)表達(dá)式進(jìn)行數(shù)據(jù)分析和篩選方式

更新時(shí)間：2024年04月02日 11:02:50 作者：Hello 阿月

這篇文章主要介紹了MySQL之使用WITH子句和臨時(shí)表達(dá)式進(jìn)行數(shù)據(jù)分析和篩選方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

使用WITH子句和臨時(shí)表達(dá)式進(jìn)行數(shù)據(jù)分析和篩選

MySQL支持使用WITH創(chuàng)建臨時(shí)表達(dá)式，通常稱為"Common Table Expressions"（CTE）。

CTE 通常用于較復(fù)雜的查詢，為復(fù)雜查詢提供了一種更清晰、模塊化的方式，以提高復(fù)雜查詢的可讀性和易維護(hù)性。

舉個(gè)通用的例子

my_cte是一個(gè)臨時(shí)表達(dá)式，它包含了從my_table中選擇滿足某個(gè)條件的列，然后，在查詢的其余部分中引用這個(gè)臨時(shí)表達(dá)式

WITH my_cte AS (
    SELECT 
        column1,
        column2
    FROM 
        my_table
    WHERE 
        condition
)
SELECT 
    *
FROM 
    my_cte
WHERE 
    column1 > 10;

WITH子句定義的CTE只在查詢執(zhí)行期間存在，不會(huì)在數(shù)據(jù)庫(kù)中永久存儲(chǔ)。

在使用時(shí)，需確保自己的MySQL版本支持WITH語(yǔ)句，因?yàn)椴煌腗ySQL版本可能對(duì)此支持程度有所不同。

舉個(gè)實(shí)際的例子

假設(shè)有一個(gè)包含員工信息的表employee：

CREATE TABLE employee (
    emp_id INT,
    emp_name VARCHAR(50),
    emp_salary DECIMAL(10, 2),
    department_id INT
);

INSERT INTO employee VALUES
(1, 'John Doe', 50000.00, 1),
(2, 'Jane Smith', 60000.00, 2),
(3, 'Bob Johnson', 75000.00, 1),
(4, 'Alice Williams', 80000.00, 2);

現(xiàn)在，需要計(jì)算每個(gè)部門(mén)的平均工資，并只選擇部門(mén)平均工資高于整體平均工資的部門(mén)員工記錄，可以使用WITH子句來(lái)實(shí)現(xiàn)：

WITH department_avg_salary AS (
    SELECT
        department_id,
        AVG(emp_salary) AS avg_salary
    FROM
        employee
    GROUP BY
        department_id
)
SELECT
    e.emp_id,
    e.emp_name,
    e.emp_salary,
    e.department_id
FROM
    employee e
JOIN
    department_avg_salary d ON e.department_id = d.department_id
WHERE
    d.avg_salary > (SELECT AVG(emp_salary) FROM employee);

在上述例子中，department_avg_salary是一個(gè)CTE，它計(jì)算每個(gè)部門(mén)的平均工資。

然后，通過(guò)JOIN將employee表與這個(gè)臨時(shí)表連接，并在最后的WHERE子句中過(guò)濾出部門(mén)平均工資高于整體平均工資的記錄，輸出結(jié)果將是符合條件的員工記錄

+--------+----------------+------------+---------------+
| emp_id | emp_name       | emp_salary | department_id |
+--------+----------------+------------+---------------+
|      3 | Bob Johnson    |   75000.00 |             1 |
|      4 | Alice Williams |   80000.00 |             2 |
+--------+----------------+------------+---------------+