文章目錄
- 問題描述
- Oracle
- MySQL
- Microsoft SQL Server
- PostgreSQL
- SQLite
- 總結
大家好，我是只談技術不剪發的 Tony 老師，
今天給大家介紹一個 SQL 實戰案例：通過橫向子查詢（LATERAL subquery）實作銷量冠軍分析，文章描述了 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 中的實作方法，
如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁
📝本文使用的示例表可以點此下載，
問題描述
公司的銷售人員負責各種產品的銷售，emp_sales 表中記錄了每個銷售人員每個月份的銷量資料，以下是該表中的一些示例資料：
SELECT * FROM emp_sales;
emp_id|sale_year|sale_month|amount |
------|---------|----------|--------|
19| 2021| 1|15672.53|
20| 2021| 1|11160.46|
21| 2021| 1|13763.75|
22| 2021| 1|11210.34|
23| 2021| 1|14610.88|
24| 2021| 1|13747.64|
25| 2021| 1|12816.20|
19| 2021| 2|14413.77|
20| 2021| 2|14266.04|
21| 2021| 2|16984.42|
...
emp_sales 表中的欄位分別表示員工編號、銷售年份、銷售月份以及銷售金額，
現在我們想要知道每個月份的銷售冠軍，也就是每個月份銷售金額最高的員工，請問應該如何使用 SQL 查詢實作？
從原理上來說，我們可以分別通過查詢找出每個月份銷量最高的員工，例如 2021 年 1 月份的銷量冠軍可以使用一下查詢陳述句：
-- MySQL、PostgreSQL 以及 SQLite
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
LIMIT 1;
-- Oracle 和 SQL Server
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
offset 0 ROWS
FETCH FIRST 1 ROWS ONLY;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統 |15672.53|
然后使用同樣的方法獲取其他月份分銷量冠軍，
顯然，這種方法不夠智能，我們需要一種查詢陳述句可以為每個月份回傳一個最高銷量的員工，下面我們就來介紹如何通過 SQL 橫向子查詢實作這一功能，
Oracle
Oracle 提供了橫向子查詢，子查詢可以使用 FROM 子句中出現在它之前的表或者查詢結果中的欄位，例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售資料中的所有年度和月份資訊，LATERAL 關鍵字表示橫向子查詢，子查詢 s 通過使用左側查詢結果中的年度和月份資料回傳了每個月銷售金額最高的員工和相應的金額，最后連接員工表獲得員工的姓名，查詢回傳的結果如下，
SALE_YEAR|SALE_MONTH|EMP_NAME|AMOUNT |
---------|----------|--------|--------|
2021| 1|龐統 |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統 |20154.83|
除了 LATERAL 關鍵字之外，Oracle 還提供了另一種實作橫向子查詢功能的 CROSS APPLY 語法，例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
該陳述句和上面的示例等價，回傳的結果也相同，
📝關于 Oracle 橫向子查詢和 CROSS APPLY 以及 OUTER APPLY 子句的詳細資訊可以參考官方檔案，
另一種實作方法就是使用視窗函式，參考下文中的 SQLite 部分，
MySQL
MySQL 8.0 提供了橫向子查詢，子查詢可以使用 FROM 子句中出現在它之前的表或者查詢結果中的欄位，例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統 |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統 |20154.83|
其中子查詢 d 的作用是獲取銷售資料中的所有年度和月份資訊，LATERAL 關鍵字表示橫向子查詢，子查詢 s 可以使用 FROM 子句中出現在它之前的表或者查詢結果中的欄位，
📝關于 MySQL 橫向子查詢的詳細資訊，可以參考官方檔案，
另一種實作方法就是使用視窗函式，參考下文中的 SQLite 部分，
Microsoft SQL Server
Microsoft SQL Server 沒有實作 LATERAL 關鍵字，但是可以使用和 Oracle 相同的 CROSS APPLY 子句實作橫向子查詢陳述句，例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month
FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售資料中的所有年度和月份資訊，CROSS APPLY 關鍵字表示子查詢 s 通過使用左側查詢結果中的年度和月份資料回傳了每個月銷售金額最高的員工和相應的金額，以上查詢通過使用左側查詢結果（d）中的年度和月份資料回傳了每個月銷售金額最高的員工和相應的金額：
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統 |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統 |20154.83|
📝關于 Microsoft SQL Server 中的 CROSS APPLY 和 OUTER APPLY 子句的詳細介紹，可以參考官方檔案，
另一種實作方法就是使用視窗函式，參考下文中的 SQLite 部分，
PostgreSQL
PostgreSQL 實作了橫向子查詢，子查詢可以使用 FROM 子句中出現在它之前的表或者查詢結果中的欄位，例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售資料中的所有年度和月份資訊，橫向子查詢 s 通過使用左側查詢結果中的年度和月份資料回傳了每個月銷售金額最高的員工和相應的金額，最后連接員工表獲得員工的姓名，查詢回傳的結果如下，
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統 |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統 |20154.83|
📝關于 PostgreSQL 橫向子查詢的詳細介紹，可以參考官方檔案，
另一種實作方法就是使用視窗函式，參考下文中的 SQLite 部分，
SQLite
SQLite 目前沒有提供橫向子查詢，因此不能通過這種方式回傳月度銷量冠軍，我們可以利用視窗函式實作同樣的功能，例如：
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM (SELECT emp_id, sale_year, sale_month, amount,
RANK() OVER (PARTITION BY sale_year, sale_month ORDER BY amount desc) AS rk
FROM emp_sales) s
JOIN employee e
ON (e.emp_id = s.emp_id)
WHERE rk = 1
ORDER BY s.sale_year, s.sale_month;
視窗函式 RANK 回傳了按照月份分組后的銷量排名，然后我們回傳了排名第一的資料，也就是月度銷量冠軍，
以上視窗函式的實作語法也適用于其他資料庫，具體介紹可以參考這篇文章，
總結
本文通過一個月度銷量冠軍分析的案例，介紹了如何利用橫向子查詢參考子查詢左側表中的欄位，另外，我們還給出了視窗函式實作相同功能的語法，
CSDN認證博客專家
資料庫架構師
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/252630.html
標籤：AI