前言

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

1. JOIN（連接查詢）

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

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

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

  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;
  

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

1.2 左連接（LEFT JOIN）

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

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

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

1.3 右連接（RIGHT JOIN）

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

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

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

1.4 自連接（Self JOIN）

• 原理：同一張表中不同記錄間的關(guān)聯(lián)查詢，通常用于查找具有層級(jí)或關(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 語(yǔ)句內(nèi)部的查詢語(yǔ)句，通常用于將一個(gè)查詢的結(jié)果作為條件或數(shù)據(jù)源。根據(jù)使用位置，子查詢可分為以下幾種：

2.1 標(biāo)量子查詢

• 特點(diǎn)：返回單個(gè)值，可以在 WHERE 或 SELECT 子句中直接使用。
• 示例：

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

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

2.2 列表子查詢

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

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

  該查詢篩選出所有來(lái)自北京的客戶的訂單。

2.3 表子查詢

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

  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)計(jì)每個(gè)客戶的訂單數(shù)量，再過(guò)濾出訂單數(shù)大于 5 的客戶。

2.4 相關(guān)子查詢

• 特點(diǎ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)槊總€(gè)銷售人員統(tǒng)計(jì)其負(fù)責(zé)的訂單數(shù)量。

3. 窗口函數(shù)

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

3.1 常見(jiàn)窗口函數(shù)

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

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

  該查詢按照訂單日期為每個(gè)訂單分配一個(gè)行號(hào)。

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

  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ù)使用，計(jì)算每個(gè)分組內(nèi)的累計(jì)值或平均值等。

  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;
  

  上述查詢展示了訂單金額的累計(jì)總和，可以用于生成報(bào)表或趨勢(shì)圖。

3.2 使用場(chǎng)景

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

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

假設(shè)你需要生成一個(gè)銷售報(bào)表，其中包含每個(gè)銷售人員的訂單總額及其在各自區(qū)域內(nèi)的排名，可以結(jié)合子查詢與窗口函數(shù)來(lái)實(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（公用表表達(dá)式）**先統(tǒng)計(jì)出每個(gè)銷售人員在各個(gè)區(qū)域內(nèi)的訂單總額，然后使用窗口函數(shù)按區(qū)域進(jìn)行分區(qū)并對(duì)總銷售額進(jìn)行排名，幫助管理者快速識(shí)別出每個(gè)區(qū)域的銷售冠軍。

5. 總結(jié)

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

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

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

最新評(píng)論