前言

在日常開發(fā)中，單表查詢往往無法滿足業(yè)務(wù)需求。通過高級查詢技術(shù)，MySQL 能夠靈活地從多個表中獲取數(shù)據(jù)、進行復(fù)雜的數(shù)據(jù)篩選和分析。本文將重點介紹三種高級查詢方式：JOIN（連接查詢）、子查詢 和 窗口函數(shù)，并提供實際案例幫助你更好地理解和應(yīng)用這些技術(shù)。

1. JOIN（連接查詢）

JOIN 允許我們在 SQL 語句中將兩個或多個表通過相關(guān)聯(lián)的列進行組合，從而在一條查詢中獲取多表數(shù)據(jù)。MySQL 中常見的 JOIN 類型包括：

1.1 內(nèi)連接（INNER JOIN）

• 原理：返回兩個表中滿足連接條件的記錄。
• 示例：

  SELECT o.order_id, o.order_date, c.customer_name
  FROM orders AS o
  INNER JOIN customers AS c ON o.customer_id = c.customer_id;
  

  上述查詢返回所有訂單及其對應(yīng)客戶名稱，僅當(dāng)訂單和客戶存在匹配關(guān)系時才會返回結(jié)果。

1.2 左連接（LEFT JOIN）

• 原理：返回左表的所有記錄，即使右表中沒有匹配也會顯示 NULL。
• 示例：

  SELECT c.customer_name, o.order_id
  FROM customers AS c
  LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
  

  此查詢列出所有客戶，即使有些客戶沒有訂單，相關(guān)訂單字段將顯示為 NULL。

1.3 右連接（RIGHT JOIN）

• 原理：與左連接類似，不過返回右表所有記錄，左表未匹配部分顯示 NULL。
• 示例：

  SELECT o.order_id, c.customer_name
  FROM orders AS o
  RIGHT JOIN customers AS c ON o.customer_id = c.customer_id;
  

  這種連接方式在實際開發(fā)中較少使用，多數(shù)場景可以通過調(diào)整 LEFT JOIN 的順序來實現(xiàn)相同效果。

1.4 自連接（Self JOIN）

• 原理：同一張表中不同記錄間的關(guān)聯(lián)查詢，通常用于查找具有層級或關(guān)系的數(shù)據(jù)。
• 示例：

  SELECT e1.employee_name AS Manager, e2.employee_name AS Subordinate
  FROM employees AS e1
  INNER JOIN employees AS e2 ON e1.employee_id = e2.manager_id;
  

  該查詢展示了管理者與其下屬之間的關(guān)系。

2. 子查詢

子查詢（Subquery）是嵌套在其他 SQL 語句內(nèi)部的查詢語句，通常用于將一個查詢的結(jié)果作為條件或數(shù)據(jù)源。根據(jù)使用位置，子查詢可分為以下幾種：

2.1 標(biāo)量子查詢

• 特點：返回單個值，可以在 WHERE 或 SELECT 子句中直接使用。
• 示例：

  SELECT order_id, order_date
  FROM orders
  WHERE customer_id = (SELECT customer_id FROM customers WHERE customer_name = '張三');
  

  此查詢將客戶名稱為“張三”的客戶 ID 提取出來，并用于過濾訂單表中的記錄。

2.2 列表子查詢

• 特點：返回一列值，可以用于 IN 或 NOT IN 條件中。
• 示例：

  SELECT order_id, order_date
  FROM orders
  WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = '北京');
  

  該查詢篩選出所有來自北京的客戶的訂單。

2.3 表子查詢

• 特點：返回一個結(jié)果集，通常用于 FROM 子句中充當(dāng)臨時表。
• 示例：

  SELECT t.customer_id, t.total_orders
  FROM (
      SELECT customer_id, COUNT(*) AS total_orders
      FROM orders
      GROUP BY customer_id
  ) AS t
  WHERE t.total_orders > 5;
  

  這里的子查詢先統(tǒng)計每個客戶的訂單數(shù)量，再過濾出訂單數(shù)大于 5 的客戶。

2.4 相關(guān)子查詢

• 特點：子查詢依賴于外層查詢的數(shù)據(jù)，每行記錄都將執(zhí)行一次子查詢。
• 示例：

  SELECT e.employee_id, e.employee_name,
         (SELECT COUNT(*) FROM orders o WHERE o.salesperson_id = e.employee_id) AS order_count
  FROM employees AS e;
  

  該查詢?yōu)槊總€銷售人員統(tǒng)計其負責(zé)的訂單數(shù)量。

3. 窗口函數(shù)

MySQL 從 8.0 版本開始支持窗口函數(shù)（Window Functions），這使得在不使用子查詢的情況下直接對查詢結(jié)果進行分組統(tǒng)計、排名等操作成為可能。

3.1 常見窗口函數(shù)

• ROW_NUMBER()：為結(jié)果集中的每一行返回一個唯一的序號。

  SELECT order_id, order_date,
         ROW_NUMBER() OVER (ORDER BY order_date) AS row_num
  FROM orders;
  

  該查詢按照訂單日期為每個訂單分配一個行號。

• RANK() 與 DENSE_RANK()：用于排名，但在存在相同值時處理方式略有不同。RANK 會跳過排名，而 DENSE_RANK 不跳過。

  SELECT customer_id, total_spent,
         RANK() OVER (ORDER BY total_spent DESC) AS rank
  FROM (
      SELECT customer_id, SUM(amount) AS total_spent
      FROM orders
      GROUP BY customer_id
  ) AS spending;
  

• SUM()、AVG()、MAX()、MIN() 等聚合函數(shù)：可以作為窗口函數(shù)使用，計算每個分組內(nèi)的累計值或平均值等。

  SELECT order_id, order_date, amount,
         SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
  FROM orders;
  

  上述查詢展示了訂單金額的累計總和，可以用于生成報表或趨勢圖。

3.2 使用場景

• 排名和排序：對銷售額、得分或其他指標(biāo)進行排序和排名。
• 累計求和：生成動態(tài)的累計值，如銷售額的逐日累加。
• 分區(qū)統(tǒng)計：在不使用 GROUP BY 的情況下，對數(shù)據(jù)進行分區(qū)統(tǒng)計，保留詳細數(shù)據(jù)行。

4. 實際案例：綜合應(yīng)用

假設(shè)你需要生成一個銷售報表，其中包含每個銷售人員的訂單總額及其在各自區(qū)域內(nèi)的排名，可以結(jié)合子查詢與窗口函數(shù)來實現(xiàn)：

WITH SalesData AS (
  SELECT salesperson_id, region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY salesperson_id, region
)
SELECT salesperson_id, region, total_sales,
       RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS sales_rank
FROM SalesData;

這里，**CTE（公用表表達式）**先統(tǒng)計出每個銷售人員在各個區(qū)域內(nèi)的訂單總額，然后使用窗口函數(shù)按區(qū)域進行分區(qū)并對總銷售額進行排名，幫助管理者快速識別出每個區(qū)域的銷售冠軍。

5. 總結(jié)

• JOIN 使得多表關(guān)聯(lián)查詢變得簡單、高效，可以通過不同類型的連接滿足各種業(yè)務(wù)需求。
• 子查詢 提供了靈活的數(shù)據(jù)篩選和過濾方式，適用于對單個數(shù)據(jù)項或整個結(jié)果集的處理。
• 窗口函數(shù) 則在 MySQL 8.0 之后引入，為數(shù)據(jù)統(tǒng)計、排名和累計計算提供了更直觀、更高效的解決方案。

通過深入掌握這三種高級查詢技術(shù)，你可以大幅提升 MySQL 查詢的復(fù)雜度與靈活性，從而更好地支持復(fù)雜業(yè)務(wù)場景和數(shù)據(jù)分析需求。歡迎在實踐中不斷嘗試和優(yōu)化，充分利用 MySQL 強大的數(shù)據(jù)處理能力！

到此這篇關(guān)于MySQL高級查詢之JOIN、子查詢、窗口函數(shù)的文章就介紹到這了,更多相關(guān)MySQL高級查詢JOIN、子查詢、窗口函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論