2024年09月08日玄貓（BlackCat）

SQL多表操作與CTE應用

本文探討 SQL 中的多表操作技巧，包含集合運算（UNION、EXCEPT、INTERSECT）、通用表表達式（CTE）的應用，以及如何使用遞迴 CTE 處理層次結構資料，並比較不同資料函式庫系統的語法差異，提供實務案例與程式碼範例。

資料函式庫 SQL

SQL CTE UNION 遞迴查詢多表查詢集合運算

在資料函式庫開發中，多表查詢與集合運算是不可或缺的技能。本文將介紹如何使用 SQL 的 UNION、EXCEPT、INTERSECT 等集合運算元，以及通用表表達式（CTE）來簡化複雜查詢，提高程式碼可讀性與執行效率。同時，我們也會探討遞迴 CTE 的應用，例如處理家族樹狀結構或填補時間序列資料的缺失值。最後，我們將比較不同資料函式庫系統（如 MySQL、Oracle、PostgreSQL、SQL Server 和 SQLite）在 CTE 語法上的差異，幫助讀者編寫跨平台相容的 SQL 查詢。

資料函式庫查詢中的多表操作與集合運算

在資料函式倉管理中，經常需要處理多張表格之間的關聯與資料合併。SQL提供了多種方法來實作這些操作，包括UNION、EXCEPT和INTERSECT等集合運算，以及通用表運算式（CTE）。

使用`UNION`合併查詢結果

UNION運算子用於合併兩個或多個SELECT陳述式的結果集。它會自動刪除重複的資料列。

合併查詢的基本規則

欄位數量必須相同：參與UNION操作的各個SELECT陳述式必須具有相同的欄位數量。
欄位名稱不必相同：各個SELECT陳述式中的欄位名稱可以不同，但第一個SELECT陳述式的欄位名稱將作為最終結果集的欄位名稱。
資料型別必須相容：各個SELECT陳述式對應欄位的資料型別應該相容。如果不相容，可以使用CAST函式進行型別轉換。

SELECT name, origin FROM staff
UNION
SELECT name, country FROM residents;

`UNION ALL`保留重複資料列

如果希望在合併結果中保留重複的資料列，可以使用UNION ALL。

SELECT name, origin FROM staff
UNION ALL
SELECT name, country FROM residents;

`UNION`與其他SQL子句的結合使用

可以在UNION操作中使用其他SQL子句，如WHERE和ORDER BY。需要注意的是，整個查詢只能有一個ORDER BY子句，且它應該放在最後。

SELECT name, origin
FROM staff
WHERE origin IS NOT NULL
UNION
SELECT name, country
FROM residents
ORDER BY name;

`EXCEPT`和`INTERSECT`運算

除了UNION之外，SQL還提供了EXCEPT和INTERSECT運算來處理多個查詢結果之間的差異和交集。

`EXCEPT`運算：找出差異

使用EXCEPT可以找出第一個查詢結果中存在但第二個查詢結果中不存在的資料列。

SELECT name FROM staff
EXCEPT
SELECT name FROM residents;

`INTERSECT`運算：找出交集

使用INTERSECT可以找出兩個查詢結果中共同存在的資料列。

SELECT name, origin FROM staff
INTERSECT
SELECT name, country FROM residents;

通用表運算式（CTE）

通用表運算式（CTE）是一種臨時結果集，可以在查詢中被參照。它分為非遞迴CTE和遞迴CTE兩種型別。

非遞迴CTE

非遞迴CTE用於簡化複雜查詢，透過將查詢結果暫存並在主查詢中參照。

WITH my_cte AS (
  SELECT name, AVG(grade) AS avg_grade
  FROM my_table
  GROUP BY name
)
SELECT *
FROM my_cte
WHERE avg_grade < 70;

遞迴CTE

遞迴CTE可以參照自身，用於處理具有層級關係或需要迭代計算的資料。

WITH RECURSIVE my_cte(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM my_cte WHERE n < 10
)
SELECT * FROM my_cte;

使用通用表表達式（CTE）提升SQL查詢效率與可讀性

在處理複雜的SQL查詢時，通用表表達式（Common Table Expression, CTE）提供了一種更清晰、更高效的方式來組織查詢邏輯。本文將探討CTE的基本用法、優勢以及在實際場景中的應用。

CTE的基本用法

CTE是一種臨時結果集，可以在SELECT、INSERT、UPDATE或DELETE陳述式中使用。它允許開發者將複雜的查詢分解為更簡單、更易於管理的部分。

簡單CTE範例

假設我們有一個員工表employees，包含員工的部門和薪水資訊。我們可以使用CTE來計算每個部門的平均薪水。

WITH avg_dept_salary AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
)
SELECT *
FROM avg_dept_salary
ORDER BY avg_salary DESC
LIMIT 1;

內容解密：

定義CTE：使用WITH關鍵字定義一個名為avg_dept_salary的CTE。
計算平均薪水：在CTE內部，使用AVG函式計算每個部門的平均薪水。
排序和限制結果：對CTE的結果進行排序，並使用LIMIT 1選取平均薪水最高的部門。

CTE與子查詢的比較

CTE與子查詢都可以實作類別似的功能，但在某些情況下，CTE具有明顯的優勢。

CTE的優勢

多次參照：一旦定義了CTE，就可以多次參照它，避免重複寫相同的子查詢。
提高可讀性：當處理多個表或複雜邏輯時，CTE可以使查詢更加清晰易懂。

WITH my_cte AS (
  SELECT id, name
  FROM customers
  WHERE country = 'USA'
)
SELECT * FROM my_cte WHERE id > 10
UNION
SELECT * FROM my_cte WHERE score > 90;

內容解密：

定義可重用的CTE：my_cte篩選出美國的客戶。
多次使用CTE：在UNION陳述式中重複使用my_cte，提高了查詢的可讀性和效率。

遞迴CTE的應用

遞迴CTE是一種特殊的CTE，可以參照自身來處理層次結構或遞迴資料。

填補資料序列中的缺失值

假設我們有一個股票價格表stock_prices，但某些日期的資料缺失。我們可以使用遞迴CTE來生成完整的日期序列，並與原表進行左連線。

WITH RECURSIVE my_dates(dt) AS (
  SELECT '2021-03-01'
  UNION ALL
  SELECT dt + INTERVAL 1 DAY
  FROM my_dates
  WHERE dt < '2021-03-06'
)
SELECT d.dt, s.price
FROM my_dates d
LEFT JOIN stock_prices s
ON d.dt = s.date;

內容解密：

生成日期序列：遞迴CTE my_dates從指定起始日期開始，逐日遞增，直到結束日期。
左連線原表：將生成的日期序列與stock_prices表進行左連線，以填補缺失的日期。

不同資料函式倉管理系統（RDBMS）中的CTE語法差異

雖然CTE的基本概念在不同RDBMS中保持一致，但具體語法可能有所不同。下表總結了在MySQL、Oracle、PostgreSQL、SQL Server和SQLite中使用CTE生成日期序列的語法差異。

RDBMS	WITH	DATE	DATE PLUS ONE	LAST DATE
MySQL	WITH RECURSIVE	‘2021-03-01’	dt + INTERVAL 1 DAY	‘2021-03-06’
Oracle	WITH	DATE ‘2021-03-01’	dt + INTERVAL ‘1’ DAY	DATE ‘2021-03-06’
PostgreSQL	WITH RECURSIVE	CAST(‘2021-03-01’ AS DATE)	CAST(dt + INTERVAL ‘1 day’ AS DATE)	‘2021-03-06’
SQL Server	WITH	CAST(‘2021-03-01’ AS DATE)	DATEADD(DAY, 1, CAST(dt AS DATE))	‘2021-03-06’
SQLite	WITH RECURSIVE	-	DATE(dt, ‘+1 day’)	‘2021-03-06’

內容解密：

跨RDBMS的語法比較：不同資料函式庫系統在處理日期計算和遞迴查詢時存在語法差異。
適用性：瞭解這些差異有助於在不同環境中編寫相容的SQL查詢。

第九章：多表查詢與遞迴查詢

在處理具有層級結構的資料時，遞迴查詢是一種非常有用的技術。本章將介紹如何使用遞迴查詢來處理具有層級結構的資料。

使用遞迴查詢處理家族樹

假設我們有一個名為 family_tree 的表格，裡麵包含了家族成員的資料。我們可以使用遞迴查詢來找出每個成員的祖先。

WITH RECURSIVE my_cte (id, name, lineage) AS (
  SELECT id, name, name AS lineage
  FROM family_tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT ft.id, ft.name, CONCAT(mc.lineage, ' > ', ft.name)
  FROM family_tree ft
  INNER JOIN my_cte mc
  ON ft.parent_id = mc.id
)
SELECT * FROM my_cte ORDER BY id;

內容解密：

WITH RECURSIVE my_cte (id, name, lineage) AS：定義了一個遞迴公用表表達式（CTE）my_cte，包含了 id、name 和 lineage 三個欄位。
第一個 SELECT 陳述式：找出根節點（即 parent_id 為 NULL 的節點），並將其 name 作為 lineage。
第二個 SELECT 陳述式：將 family_tree 表格與 my_cte 進行內連線，找出每個節點的子節點，並將子節點的 name 新增到 lineage 中。
UNION ALL：將兩個 SELECT 陳述式的結果合併起來。

不同資料函式倉管理系統的遞迴查詢語法差異

不同的資料函式倉管理系統（RDBMS）對遞迴查詢的語法有所不同。以下是各大 RDBMS 的遞迴查詢語法：

RDBMS	WITH	NAME	LINEAGE
MySQL	WITH RECURSIVE	name	CONCAT(mc.lineage, ’ > ‘, ft.name)
Oracle	WITH	name	mc.lineage \|\| ’ > ’ \|\| ft.name
PostgreSQL	WITH RECURSIVE	CAST(name AS VARCHAR(30))	CAST(CONCAT(mc.lineage, ’ > ‘, ft.name) AS VARCHAR(30))
SQL Server	WITH	CAST(name AS VARCHAR(30))	CAST(CONCAT(mc.lineage, ’ > ‘, ft.name) AS VARCHAR(30))
SQLite	WITH RECURSIVE	name	mc.lineage \|\| ’ > ’ \|\| ft.name

第十章：常見 SQL 問題

本章將介紹一些常見的 SQL 問題及其解決方法。

找出具有重複值的列

假設我們有一個名為 teas 的表格，裡麵包含了茶的名字和沖泡溫度。我們可以使用以下查詢來找出具有重複值的列：

WITH dup_rows AS (
  SELECT tea, temperature, COUNT(*) as num_rows
  FROM teas
  GROUP BY tea, temperature
  HAVING COUNT(*) > 1
)
SELECT t.id, d.tea, d.temperature
FROM teas t
INNER JOIN dup_rows d
ON t.tea = d.tea AND t.temperature = d.temperature;

內容解密：

WITH dup_rows AS：定義了一個公用表表達式（CTE）dup_rows，包含了具有重複值的列。
GROUP BY tea, temperature：根據 tea 和 temperature 進行分組。
HAVING COUNT(*) > 1：篩選出具有重複值的列。
INNER JOIN：將 teas 表格與 dup_rows 進行內連線，找出具有重複值的列的詳細資料。

選擇具有最大值的列

假設我們有一個名為 sales 的表格，裡麵包含了銷售資料。我們可以使用以下查詢來選擇具有最大值的列：

SELECT *
FROM sales
WHERE (employee, date) IN (
  SELECT employee, MAX(date)
  FROM sales
  GROUP BY employee
);

內容解密：

SELECT employee, MAX(date)：找出每個員工的最大銷售日期。
GROUP BY employee：根據員工進行分組。
(employee, date) IN：篩選出具有最大銷售日期的列。

本章介紹了常見的 SQL 問題及其解決方法，包括找出具有重複值的列和選擇具有最大值的列。這些技術可以幫助您更好地處理和分析資料。

資料函式庫查詢與操作技術

查詢每個員工最近銷售記錄的銷售數量

要查詢每個員工最近的銷售記錄，可以透過以下 SQL 查詢實作：

SELECT s.id, r.employee, r.recent_date, s.sales
FROM (SELECT employee, MAX(date) AS recent_date
      FROM sales
      GROUP BY employee) r
INNER JOIN sales s
ON r.employee = s.employee
AND r.recent_date = s.date;

內容解密：

子查詢（Subquery）：首先，我們使用子查詢來找出每個員工最近的銷售日期。
- SELECT employee, MAX(date) AS recent_date FROM sales GROUP BY employee：這行 SQL 查詢每個員工的最近銷售日期。
內連線（INNER JOIN）：然後，我們將子查詢的結果與 sales 表進行內連線，以取得完整的銷售記錄。
- INNER JOIN sales s ON r.employee = s.employee AND r.recent_date = s.date：這行 SQL 將子查詢結果 r 與 sales 表 s 連線起來，條件是員工和日期都匹配。

合併多欄位文字為單一欄位

情境一：合併同一列中的多欄位文字

假設我們有一張表，包含 id 和 name 兩個欄位，我們想將它們合併成一個新的欄位 id_name。

-- 適用於 MySQL, PostgreSQL 和 SQL Server
SELECT CONCAT(id, '_', name) AS id_name
FROM my_table;

-- 適用於 Oracle, PostgreSQL, 和 SQLite
SELECT id || '_' || name AS id_name
FROM my_table;

內容解密：

CONCAT 函式：用於將多個字串合併成一個。
- CONCAT(id, '_', name)：將 id、'_' 和 name 合併成一個字串。
|| 運算子：在某些資料函式庫系統中，用於字串的連線。

情境二：合併多列中的文字為單一欄位

假設我們有一張表，記錄了每個人消耗的卡路里，我們想將每個人的卡路里消耗記錄合併成一個字串。

SELECT name,
       GROUP_CONCAT(calories) AS calories_list
FROM workouts
GROUP BY name;

內容解密：

GROUP_CONCAT 函式：用於將分組後的資料合併成一個字串列表。
- GROUP_CONCAT(calories)：將每個人的卡路里消耗記錄合併成一個以逗號分隔的字串。
分組（GROUP BY）：根據 name 欄位對資料進行分組。

查詢包含特定欄位名稱的所有表格

在大多數資料函式倉管理系統中，有一個特殊的表格包含了所有表格的名稱和欄位名稱。我們可以查詢這個表格來找到包含特定欄位名稱的所有表格。

-- 適用於 MySQL
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%city%'
AND table_schema = 'my_db_name';

-- 適用於 Oracle
SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%CITY%'
AND owner = 'MY_USER_NAME';

-- 適用於 PostgreSQL 和 SQL Server
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%city%'
AND table_catalog = 'my_db_name';

內容解密：

information_schema.columns：在 MySQL、PostgreSQL 和 SQL Server 中，這是一個包含了所有表格欄位資訊的系統檢視表。
all_tab_columns：在 Oracle 中，這是一個包含了所有表格欄位資訊的系統檢視表。

更新表格中與另一表格具有相同識別碼的資料

假設我們有兩張表格：products 和 deals，我們想根據 id 更新 deals 表格中的 name 欄位，使其與 products 表格中的 name 相對應。

UPDATE deals
SET name = (SELECT name FROM products WHERE products.id = deals.id)
WHERE EXISTS (SELECT 1 FROM products WHERE products.id = deals.id);

內容解密：

UPDATE 陳述式：用於更新表格中的資料。
SET 子句：指定要更新的欄位及其新值。
WHERE EXISTS 子句：確保只更新那些在 products 表格中有匹配 id 的記錄。

SQL 資料表更新技術

在資料函式倉管理中，更新資料表是一項常見且重要的操作。本文將探討如何根據另一張資料表的識別碼更新現有資料表的欄位值，並提供不同關聯式資料函式倉管理系統（RDBMS）下的實作範例。

更新資料表的挑戰

假設我們有兩張資料表：deals 和 products。我們的目標是根據 id 的匹配，將 products 表中的 name 欄位值更新到 deals 表的對應紀錄中。初始資料如下：

+
---
---
+
---
-
---
-
---
---
+
| id   | name         |
+
---
---
+
---
-
---
-
---
---
+
| 102  | Tech gift    |
| 103  | Holiday card |
+
---
---
+
---
-
---
-
---
---
+

期望結果：

+
---
---
+
---
-
---
-
---
-
---
-
---
+
| id   | name              |
+
---
---
+
---
-
---
-
---
-
---
-
---
+
| 102  | MIDI keyboard     |
| 103  | Mother's day card |
+
---
---
+
---
-
---
-
---
-
---
-
---
+

不同 RDBMS 的更新語法

MySQL

MySQL 使用逗號分隔多張資料表來進行更新操作：

UPDATE deals d, products p
SET d.name = p.name
WHERE d.id = p.id;

Oracle

Oracle 資料函式庫使用子查詢來實作相同的功能：

UPDATE deals d
SET name = (SELECT p.name
            FROM products p
            WHERE d.id = p.id);

PostgreSQL 和 SQLite

這兩種資料函式庫支援使用 FROM 子句和 JOIN 來更新資料表：

UPDATE deals
SET name = p.name
FROM deals d
INNER JOIN products p
ON d.id = p.id
WHERE deals.id = d.id;

SQL Server

SQL Server 的更新語法與 PostgreSQL 類別似，但寫法稍有不同：

UPDATE d
SET d.name = p.name
FROM deals d
INNER JOIN products p
ON d.id = p.id;

重點注意事項

不可逆操作：執行 UPDATE 後無法直接復原，除非事先啟動了事務處理。
測試優先：在正式環境執行前，務必在測試環境驗證 SQL 語法的正確性。
備份資料：更新操作前應備份相關資料表，以防萬一。

進一步的學習資源

欲瞭解更多 SQL 更新操作的細節，請參考相關檔案或實踐手冊，包括但不限於：

更新特定欄位值
使用查詢結果更新資料表
新增欄位到現有資料表

結語

SQL 語言強大且靈活，但不同 RDBMS 之間的語法差異要求開發者具備良好的適應能力。持續的學習和實踐是掌握 SQL 的關鍵。希望本文能為您的資料函式倉管理工作提供實質性的幫助。

資料函式庫查詢中的多表操作與集合運算

使用UNION合併查詢結果

合併查詢的基本規則

UNION ALL保留重複資料列

UNION與其他SQL子句的結合使用

EXCEPT和INTERSECT運算

EXCEPT運算：找出差異

INTERSECT運算：找出交集

通用表運算式（CTE）

非遞迴CTE

遞迴CTE

使用通用表表達式（CTE）提升SQL查詢效率與可讀性

CTE的基本用法

簡單CTE範例

內容解密：

CTE與子查詢的比較

CTE的優勢

內容解密：

遞迴CTE的應用

填補資料序列中的缺失值

內容解密：

不同資料函式倉管理系統（RDBMS）中的CTE語法差異

內容解密：

第九章：多表查詢與遞迴查詢

使用遞迴查詢處理家族樹

內容解密：

不同資料函式倉管理系統的遞迴查詢語法差異

第十章：常見 SQL 問題

找出具有重複值的列

內容解密：

選擇具有最大值的列

內容解密：

資料函式庫查詢與操作技術

查詢每個員工最近銷售記錄的銷售數量

內容解密：

合併多欄位文字為單一欄位

情境一：合併同一列中的多欄位文字

內容解密：

情境二：合併多列中的文字為單一欄位

內容解密：

查詢包含特定欄位名稱的所有表格

內容解密：

更新表格中與另一表格具有相同識別碼的資料

內容解密：

SQL 資料表更新技術

更新資料表的挑戰

不同 RDBMS 的更新語法

MySQL

Oracle

PostgreSQL 和 SQLite

SQL Server

重點注意事項

進一步的學習資源

結語

使用`UNION`合併查詢結果

`UNION ALL`保留重複資料列

`UNION`與其他SQL子句的結合使用

`EXCEPT`和`INTERSECT`運算

`EXCEPT`運算：找出差異

`INTERSECT`運算：找出交集