SQL 排序查詢在資料函式庫操作中扮演著至關重要的角色，它允許我們按照指定的欄位和順序排列查詢結果，使資料更易於閱讀和分析。除了基本的單欄位排序外，SQL 還支援更進階的排序方式，例如多欄位排序、條件排序以及子字串排序。這些技巧可以幫助我們更精確地控制結果集的呈現方式，滿足不同的資料分析需求。在實際應用中，我們經常需要處理多個表格之間的資料關聯。SQL 提供了 JOIN 和集合運算等強大的工具，可以幫助我們有效地合併和分析來自不同表格的資料。理解和掌握這些多表操作技巧，對於撰寫複雜的 SQL 查詢至關重要，例如找出兩個表格的共同行、差異行，以及根據關聯條件進行資料篩選和聚合。
排序查詢結果
本章重點在於自訂查詢結果的呈現方式。透過瞭解如何控制結果集的組織方式，您可以提供更具可讀性和意義的資料。
以指定順序傳回查詢結果
問題描述
您希望根據薪水（從最低到最高）顯示部門10中員工的姓名、職位和薪水。您希望傳回以下結果集：
ENAME JOB SAL
---
---
-
---
-
---
--
---
-
---
MILLER CLERK 1300
CLARK MANAGER 2450
KING PRESIDENT 5000
解決方案
使用ORDER BY子句：
SELECT ename, job, sal
FROM emp
WHERE deptno = 10
ORDER BY sal ASC;
詳細解析：
ORDER BY子句允許您對結果集的行進行排序。上述解決方案根據SAL欄位以升序排序行。預設情況下，ORDER BY將按升序排序，因此ASC子句是可選的。另外，您也可以指定DESC以按降序排序：
SELECT ename, job, sal
FROM emp
WHERE deptno = 10
ORDER BY sal DESC;
這將傳回：
ENAME JOB SAL
---
---
-
---
-
---
--
---
-
---
KING PRESIDENT 5000
CLARK MANAGER 2450
MILLER CLERK 1300
您不需要指定排序所依據的欄位名稱，而是可以指定代表欄位的數字。該數字從1開始，與SELECT列表中的專案從左到右匹配。例如：
SELECT ename, job, sal
FROM emp
WHERE deptno = 10
ORDER BY 3 DESC;
這裡的3對應於SELECT列表中的第三個欄位，即SAL。
多欄位排序
問題描述
您希望首先按DEPTNO升序，然後按薪水降序對EMP表中的行進行排序。您希望傳回以下結果集：
EMPNO DEPTNO SAL ENAME JOB
---
---
---
---
---
--
---
---
-
---
-
---
--
7839 10 5000 KING PRESIDENT
7782 10 2450 CLARK MANAGER
7934 10 1300 MILLER CLERK
7788 20 3000 SCOTT ANALYST
7902 20 3000 FORD ANALYST
7566 20 2975 JONES MANAGER
7876 20 1100 ADAMS CLERK
7369 20 800 SMITH CLERK
7698 30 2850 BLAKE MANAGER
7499 30 1600 ALLEN SALESMAN
7844 30 1500 TURNER SALESMAN
7521 30 1250 WARD SALESMAN
7654 30 1250 MARTIN SALESMAN
7900 30 950 JAMES CLERK
解決方案
在ORDER BY子句中列出不同的排序欄位，以逗號分隔：
SELECT empno, deptno, sal, ename, job
FROM emp
ORDER BY deptno, sal DESC;
詳細解析：
ORDER BY中的優先順序從左到右。如果您使用SELECT列表中欄位的數字位置進行排序，則該數字不得大於SELECT列表中的專案數。通常，您可以按不在SELECT列表中的欄位排序，但必須明確命名該欄位。但是，如果您在查詢中使用了GROUP BY或DISTINCT，則不能按不在SELECT列表中的欄位排序。
按子字串排序
問題描述
您希望根據字串的特定部分對查詢結果進行排序。例如，您希望從表EMP中傳回員工姓名和職位，並按JOB欄位的最後兩個字元進行排序。結果集應如下所示：
ENAME JOB
---
---
-
---
-
---
--
KING PRESIDENT
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
JONES MANAGER
CLARK MANAGER
BLAKE MANAGER
ALLEN SALESMAN
MARTIN SALESMAN
WARD SALESMAN
TURNER SALESMAN
SCOTT ANALYST
FORD ANALYST
解決方案（適用於DB2、MySQL、Oracle和PostgreSQL）
在ORDER BY子句中使用SUBSTR函式：
SELECT ename, job
FROM emp
ORDER BY SUBSTR(job, LENGTH(job) - 1);
詳細解析：
使用資料函式倉管理系統的子字串函式，您可以輕鬆地按字串的任何部分進行排序。要按字串的最後兩個字元排序，請找到字串的末尾（即字串的長度）並減去2。起始位置將是字串的倒數第二個字元。然後，您取該起始位置之後的所有字元。SQL Server的SUBSTRING函式與其他資料函式庫的函式不同，因為它需要第三個引數來指定要取的字元數。在這個例子中，任何大於或等於2的數字都將有效。
處理混合字母和數字的資料排序
圖表翻譯：
此圖示展示了混合字母和數字資料在不同資料函式倉管理系統中的處理方式。
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title SQL排序查詢與多表操作技巧
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml圖表翻譯： 上述圖表展示了在不同資料函式庫系統中，如何使用特定的函式處理並排序混合字母和數字的資料。
詳細解析：
對於Oracle、SQL Server和PostgreSQL，可以使用REPLACE和TRANSLATE函式來修改字串以進行排序。對於DB2，由於其隱式型別轉換更為嚴格，因此需要將DEPTNO轉換為CHAR型別。這兩個解決方案都能有效地按特定部分對混合資料進行排序。
資料排序中的空值處理
在進行資料排序時，經常會遇到欄位中包含空值（NULL）的情況。如何在排序結果中控制這些空值的顯示順序，是一個常見的問題。本文將討論如何在不同的資料函式庫系統中處理空值的排序問題。
問題描述
假設我們有一個員薪水料表（EMP），其中包含員工姓名（ENAME）、薪水（SAL）以及手續費（COMM）等欄位。手續費欄位可能包含空值。我們希望能夠根據手續費欄位進行排序，並且能夠控制空值是在排序結果的前面還是後面。
解決方案
不同的資料函式庫系統對於空值的排序處理方式有所不同。以下是針對不同資料函式庫系統的解決方案：
DB2、MySQL、PostgreSQL 和 SQL Server
在這些資料函式庫系統中，可以使用 CASE 運算式來建立一個輔助欄位，用於控制空值的排序順序。
-- 非空手續費升序排序，空值排在最後
SELECT ename, sal, comm
FROM (
SELECT ename, sal, comm,
CASE WHEN comm IS NULL THEN 0 ELSE 1 END AS is_null
FROM emp
) x
ORDER BY is_null DESC, comm;
-- 非空手續費降序排序，空值排在最後
SELECT ename, sal, comm
FROM (
SELECT ename, sal, comm,
CASE WHEN comm IS NULL THEN 0 ELSE 1 END AS is_null
FROM emp
) x
ORDER BY is_null DESC, comm DESC;
-- 非空手續費升序排序，空值排在最前
SELECT ename, sal, comm
FROM (
SELECT ename, sal, comm,
CASE WHEN comm IS NULL THEN 0 ELSE 1 END AS is_null
FROM emp
) x
ORDER BY is_null, comm;
-- 非空手續費降序排序，空值排在最前
SELECT ename, sal, comm
FROM (
SELECT ename, sal, comm,
CASE WHEN comm IS NULL THEN 0 ELSE 1 END AS is_null
FROM emp
) x
ORDER BY is_null, comm DESC;
Oracle
Oracle 資料函式庫提供了 NULLS FIRST 和 NULLS LAST 語法，可以直接在 ORDER BY 子句中控制空值的排序順序。
-- 非空手續費升序排序，空值排在最後
SELECT ename, sal, comm
FROM emp
ORDER BY comm NULLS LAST;
-- 非空手續費升序排序，空值排在最前
SELECT ename, sal, comm
FROM emp
ORDER BY comm NULLS FIRST;
-- 非空手續費降序排序，空值排在最前
SELECT ename, sal, comm
FROM emp
ORDER BY comm DESC NULLS FIRST;
程式碼解密：
- CASE 運算式的使用：在 DB2、MySQL、PostgreSQL 和 SQL Server 中，使用 CASE 運算式來判斷手續費欄位是否為空，並賦予一個輔助欄位
is_null值。根據is_null的值來控制空值的排序順序。 - Oracle 的 NULLS FIRST 和 NULLS LAST 語法：Oracle 提供了直接控制空值排序順序的語法，可以在
ORDER BY子句中使用NULLS FIRST或NULLS LAST來指定空值的排序位置。 - 子查詢的使用：在 DB2、MySQL、PostgreSQL 和 SQL Server 的解決方案中，使用子查詢來建立包含輔助欄位的結果集，然後對這個結果集進行排序。
進階SQL查詢技巧：排序與多表操作
在資料函式庫查詢中，排序與多表操作是兩項基本且重要的技能。本文將探討SQL中的排序機制以及如何有效地結合多個表格的資料。
2.6 根據資料相關鍵值排序
在某些情況下，我們需要根據特定的條件邏輯進行排序。例如，當員工的職位是SALESMAN時，我們希望根據COMM（手續費）進行排序；否則，則根據SAL（薪水）排序。
問題描述
給定EMP表格，需要根據JOB欄位的值決定排序依據。如果JOB是SALESMAN，則按COMM排序；否則，按SAL排序。
解決方案
使用CASE表示式於ORDER BY子句中：
SELECT ename, sal, job, comm
FROM emp
ORDER BY CASE WHEN job = 'SALESMAN' THEN comm ELSE sal END;
內容解密：
CASE WHEN job = 'SALESMAN' THEN comm ELSE sal END：此表示式動態決定排序的依據。如果員工的職位是SALESMAN，則使用COMM進行排序；否則，使用SAL。ORDER BY子句根據上述CASE表示式的結果進行排序。
第3章：處理多個表格
本章將介紹如何使用JOIN和集合運算來合併來自多個表格的資料。這些技術是SQL的基礎，對於撰寫複雜查詢至關重要。
3.1 疊加多個結果集
有時，我們需要將多個表格的資料合併，概念上是將一個結果集疊加在另一個結果集之上。這些表格不一定具有共同的鍵，但它們的欄位具有相同的資料型別。
問題描述
需要將EMP表格中部門10的員工姓名和部門編號與DEPT表格中的部門名稱和部門編號合併顯示。
解決方案
使用UNION ALL集合運算子合並多個表格的行：
SELECT ename AS ename_and_dname, deptno
FROM emp
WHERE deptno = 10
UNION ALL
SELECT '
---
-
---
---
', NULL
FROM t1
UNION ALL
SELECT dname, deptno
FROM dept;
內容解密：
UNION ALL：此運算子將多個查詢的結果集合併為一個結果集，並保留重複的行。- `SELECT '
‘, NULL FROM t1`：此查詢用於在結果集中插入一行分隔符號。 3. 每個SELECT陳述式中的欄位數量和資料型別必須匹配。
3.2 合併相關行
當需要根據共同欄位合併多個表格的行時，可以使用JOIN操作。
問題描述
需要顯示部門10中所有員工的名字以及他們所在部門的位置。
解決方案
將EMP表格與DEPT表格根據DEPTNO進行JOIN：
SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.deptno = 10;
內容解密：
FROM emp e, dept d：此語法表示對EMP和DEPT表格進行笛卡爾積，並分別賦予別名e和d。WHERE e.deptno = d.deptno：此條件確保只傳回DEPTNO匹配的行，即進行了等值JOIN。AND e.deptno = 10：此條件進一步篩選出部門10的員工。
多表操作與查詢技術
在資料函式庫查詢中，處理多表之間的關係是一項基本且重要的技能。本章節將介紹如何有效地進行多表查詢，包括使用 JOIN 進行表連線、找出兩個表之間的共同行、以及從一個表中檢索出另一個表中不存在的值。
使用 JOIN 進行表連線
當需要從多個表中檢索資料時，JOIN 操作是不可或缺的。JOIN 允許我們根據特定的條件將兩個或多個表中的行結合起來。
內部 JOIN（INNER JOIN）
內部 JOIN 是最常見的 JOIN 型別，它傳回兩個表中滿足 JOIN 條件的行。以下是一個使用內部 JOIN 的例子：
SELECT e.ename, d.loc
FROM emp e INNER JOIN dept d
ON (e.deptno = d.deptno)
WHERE e.deptno = 10;
內容解密：
SELECT e.ename, d.loc：選擇員工姓名和部門位置。FROM emp e INNER JOIN dept d：對emp表和dept表進行內部 JOIN 操作，分別賦予別名e和d。ON (e.deptno = d.deptno)：指定 JOIN 條件，即當兩個表的deptno相等時進行連線。WHERE e.deptno = 10：過濾出部門編號為 10 的員工。
找出兩個表之間的共同行
在某些情況下，我們需要找出兩個表之間共有的行。這可以透過在多個列上進行 JOIN 操作來實作，或者使用集合運算子如 INTERSECT。
使用 JOIN 找出共同行
SELECT e.empno, e.ename, e.job, e.sal, e.deptno
FROM emp e JOIN V
ON (e.ename = V.ename AND e.job = V.job AND e.sal = V.sal);
內容解密：
SELECT e.empno, e.ename, e.job, e.sal, e.deptno：選擇員工編號、姓名、工作、薪水和部門編號。FROM emp e JOIN V：對emp表和檢視V進行 JOIN 操作。ON (e.ename = V.ename AND e.job = V.job AND e.sal = V.sal)：指定 JOIN 條件，即當員工姓名、工作和薪水都匹配時。
使用 INTERSECT 找出共同行
對於支援 INTERSECT 集合運算的資料函式庫，可以使用以下查詢：
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE (ename, job, sal) IN (
SELECT ename, job, sal FROM emp
INTERSECT
SELECT ename, job, sal FROM V
);
內容解密：
- 使用
INTERSECT傳回兩個查詢結果的交集，即兩個表中共有的行。 WHERE (ename, job, sal) IN (...)：過濾出在交集中的行。
從一個表中檢索出另一個表中不存在的值
有時，我們需要找出一個表中存在但另一個表中不存在的值。這可以透過使用集合運算子如 EXCEPT 或 MINUS，或者使用子查詢來實作。
使用 EXCEPT 或 MINUS
-- 對於 DB2、PostgreSQL 和 SQL Server
SELECT deptno FROM dept
EXCEPT
SELECT deptno FROM emp;
-- 對於 Oracle
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
內容解密：
EXCEPT或MINUS用於傳回第一個查詢結果中存在但第二個查詢結果中不存在的行。- 這裡用於找出
dept表中存在但emp表中不存在的部門編號。
使用子查詢
對於不支援集合運算的資料函式庫，可以使用子查詢：
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);
內容解密：
SELECT deptno FROM dept WHERE deptno NOT IN (...)：選擇dept表中部門編號不在子查詢結果中的行。- 子查詢傳回
emp表中所有的部門編號。
