返回文章列表
玄貓（BlackCat）

SQL查詢SELECT陳述式進階應用

本文探討SQL SELECT陳述式的進階用法，包含欄位別名、表格別名、子查詢、DISTINCT關鍵字、JOIN操作的型別與應用，以及GROUP BY、HAVING、ORDER BY等子句的綜合運用，有效提升查詢效率與程式碼可讀性。

資料函式庫 SQL
SQL查詢 SELECT陳述式 子查詢 JOIN DISTINCT GROUP BY

SQL 的 SELECT 陳述式除了基本查詢功能外，還支援許多進階用法，能大幅提升查詢效率和程式碼可讀性。透過欄位別名和表格別名，可以簡化複雜查詢的程式碼，讓 SQL 更易於理解和維護。子查詢允許巢狀查詢，方便處理多表格關聯或複雜計算，例如統計每個旅遊路線的停靠點數量。DISTINCT 關鍵字則能有效移除重複結果，確保資料的唯一性。JOIN 操作是關聯式資料函式庫的核心功能，可以合併多個表格的資料，其中 ON 子句定義了表格間的關聯條件，而不同型別的 JOIN 操作（如 INNER JOIN、LEFT JOIN、RIGHT JOIN）則可以根據需求傳回不同的結果集。此外，GROUP BY 子句可以根據指定欄位分組資料，HAVING 子句則可以過濾分組後的結果，ORDER BY 子句可以排序查詢結果，而 LIMIT 子句可以限制傳回的資料量，這些子句的綜合運用可以滿足各種複雜的查詢需求。

SQL查詢基礎：SELECT陳述式的進階應用

在SQL查詢中，SELECT陳述式是使用最頻繁的陳述式之一，用於從資料函式庫表中檢索資料。本文將探討SELECT陳述式的一些進階應用，包括欄位別名、表格別名、子查詢以及關鍵字DISTINCT的使用。

欄位別名與表格別名

在撰寫SQL查詢時，為欄位或表格設定別名可以提高查詢的可讀性。

欄位別名

欄位別名允許你在查詢結果中為欄位指定一個臨時名稱。這在處理複雜查詢或需要簡化欄位名稱時非常有用。

SELECT id AS "Waterfall #", name AS "Waterfall Name"
FROM waterfall;

在這個例子中，id欄位被別名為"Waterfall #"，而name欄位被別名為"Waterfall Name"。

表格別名

表格別名則允許你為表格指定一個簡短的名稱，方便在查詢中參照。

SELECT o.id, o.name
FROM owner o;

在這裡，owner表格被賦予了別名o，使得查詢更加簡潔。

子查詢

子查詢是一個巢狀在另一個查詢中的查詢。它可以用於從多個表格中檢索資料，或進行複雜的計算。

簡單子查詢

假設我們想要在查詢結果中包含所有縣的平均人口數，可以使用子查詢。

SELECT id, name, population,
(SELECT AVG(population) FROM county) AS average_pop
FROM county;

這個查詢會傳回每個縣的id、name、population，以及所有縣的平均人口數。

相關子查詢與非相關子查詢

子查詢可以分為相關子查詢和非相關子查詢。非相關子查詢不依賴於外部查詢，可以獨立執行。相關子查詢則依賴於外部查詢中的值，因此對於外部查詢的每一行資料，相關子查詢都會執行一次。

-- 相關子查詢
SELECT o.id, o.name,
(SELECT COUNT(*) FROM waterfall w WHERE o.id = w.owner_id) AS num_waterfalls
FROM owner o;

這個相關子查詢對於每個業主，計算其擁有的瀑布數量。然而，由於效能問題，通常建議將相關子查詢重寫為JOIN操作。

-- 使用JOIN重寫相關子查詢
SELECT o.id, o.name, COUNT(w.id) AS num_waterfalls
FROM owner o LEFT JOIN waterfall w ON o.id = w.owner_id
GROUP BY o.id, o.name;

使用DISTINCT關鍵字

預設情況下，SELECT陳述式會傳回所有符合條件的行。如果你只想要取得不重複的結果，可以使用DISTINCT關鍵字。

SELECT DISTINCT o.type, w.open_to_public
FROM owner o JOIN waterfall w ON o.id = w.owner_id;

這個查詢會傳回所有不同的type和open_to_public組合。

SQL查詢基礎與進階應用

DISTINCT關鍵字的使用

在SQL查詢中，若要移除結果中的重複行，可以使用DISTINCT關鍵字。以下查詢傳回type和open_to_public的唯一組合：

SELECT DISTINCT o.type, w.open_to_public
FROM owner o
JOIN waterfall w ON o.id = w.owner_id;

結果如下：

type        open_to_public
---
-
---
-    
---
-
---
-
---
-
---
public      y
private     y

COUNT與DISTINCT的結合使用

若要計算某列的唯一值數量，可以結合使用COUNT和DISTINCT。以下查詢傳回type列的唯一值數量：

SELECT COUNT(DISTINCT type) AS unique_count
FROM owner;

結果如下：

unique_count
---
-
---
2

計算多列的唯一組合數量

若要計算多列的唯一組合數量，可以將DISTINCT查詢巢狀在子查詢中，然後對子查詢進行COUNT。以下查詢傳回type和open_to_public的唯一組合數量：

SELECT COUNT(*) AS num_unique
FROM (
  SELECT DISTINCT o.type, w.open_to_public
  FROM owner o 
  JOIN waterfall w ON o.id = w.owner_id
) my_subquery;

結果如下：

num_unique
---
-
---
-
---
2

MySQL和PostgreSQL支援直接在COUNT(DISTINCT)中使用多列。以下查詢與前述查詢等效：

-- MySQL版本
SELECT COUNT(DISTINCT o.type, w.open_to_public) AS num_unique
FROM owner o 
JOIN waterfall w ON o.id = w.owner_id;

-- PostgreSQL版本
SELECT COUNT(DISTINCT (o.type, w.open_to_public)) AS num_unique
FROM owner o 
JOIN waterfall w ON o.id = w.owner_id;

程式碼解析：

  1. SELECT COUNT(DISTINCT column_name)：計算指定列的唯一值數量。
  2. 子查詢中的DISTINCT：先取得多列的唯一組合，再計算總數。
  3. 資料函式庫特定語法：MySQL和PostgreSQL對多列COUNT(DISTINCT)的支援語法略有不同。

FROM子句的應用

指定資料來源

FROM子句用於指定查詢的資料來源，可以是單一表或檢視。若需要明確資料函式庫或架構，可以使用點符號（.）進行標示。以下查詢從sqlbook架構中的waterfall表取得資料：

SELECT name
FROM sqlbook.waterfall;

多表資料的聯合查詢

若要從多個表中取得資料，最常見的方法是使用JOIN。以下查詢結合waterfall和tour表，並輸出單一結果表：

SELECT *
FROM waterfall w 
JOIN tour t ON w.id = t.stop;

結果如下：

id  name           ...  name      stop  ...
---
--  
---
-
---
-
---
---
-  
---
-
---
--  
---
--
1    Munising Falls  ...  M-28      1
1    Munising Falls  ...  Munising  1
2    Tannery Falls   ...  Munising  2
3    Alger Falls     ...  M-28      3
3    Alger Falls     ...  Munising  3
...

JOIN操作的詳解

表別名（Table Alias）

在查詢中，可以為表指定臨時名稱（別名），以簡化參照。以下陳述式為waterfall和tour表分別指定別名w和t：

waterfall w JOIN tour t

JOIN與ON子句的結合使用

JOIN用於合併多個表，而ON子句則定義了表的關聯條件。以下查詢透過w.id = t.stop將兩表連線：

waterfall w JOIN tour t
ON w.id = t.stop;

結果表的結構

合併後的結果表包含所有參與表的列。若多表中有相同名稱的列，需使用表別名加以區分。例如，兩個表中都有名為name的列，在查詢時需明確其來源：

SELECT w.name AS waterfall_name, t.name AS tour_name
FROM waterfall w 
JOIN tour t ON w.id = t.stop;

結果如下：

waterfall_name  tour_name
---
-
---
-
---
---
- 
---
-
---
---
Munising Falls  M-28
Munising Falls  Munising
Tannery Falls   Munising
Alger Falls     M-28
Alger Falls     Munising
...

不同型別的JOIN操作

預設情況下，JOIN表示INNER JOIN，即只傳回兩表中匹配的行。若要傳回某個表中所有行，需使用其他型別的JOIN（如LEFT JOIN或RIGHT JOIN）。具體JOIN型別的應用將在第9章進一步討論。

子查詢在FROM子句中的應用

子查詢可以作為臨時表使用於FROM子句中。這種查詢方式稱為衍生表（Derived Table）。以下查詢先篩選出type='public'的所有者，然後與waterfall表進行連線：

SELECT w.name AS waterfall_name, o.name AS owner_name
FROM (
  SELECT * FROM owner WHERE type = 'public'
) o 
JOIN waterfall w ON o.id = w.owner_id;

結果如下：

waterfall_name  owner_name
---
-
---
-
---
---
- 
---
-
---
-
---
-
---
Little Miners   Pictured Rocks
Miners Falls    Pictured Rocks
Munising Falls  Pictured Rocks
Wagner Falls    MI DNR

程式碼解析：

  1. FROM (SELECT ...)：子查詢作為衍生表參與主查詢。
  2. 子查詢需具備獨立性：子查詢應能獨立執行，不依賴外部查詢。
  3. 為子查詢結果指定別名：如上述範例中的o。

使用子查詢簡化複雜任務

在SQL查詢中，子查詢（Subquery）是一種強大的工具，能夠將複雜的任務分解為更小的步驟，從而簡化查詢邏輯並提升可讀性。本文將探討子查詢在FROM子句中的應用，以及與WITH子句的比較。

子查詢在FROM子句中的應用

在FROM子句中使用子查詢，可以將其視為一個臨時表，然後對這個臨時表進行進一步的查詢操作。這種方法在處理複雜查詢或需要先過濾資料再進行連線操作時尤其有用。

語法範例

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM (SELECT * FROM owner WHERE type = 'public') o
JOIN waterfall w ON o.id = w.owner_id;

內容解密：

  1. (SELECT * FROM owner WHERE type = 'public')：這是一個子查詢，用於篩選出type為'public'的擁有者記錄。
  2. 將子查詢結果命名為o，然後與waterfall表進行連線操作。
  3. JOIN條件是o.id = w.owner_id，確保了正確的匹配。

為什麼使用FROM子句中的子查詢？

使用子查詢的主要優勢在於能夠將大任務分解為更小的步驟。例如：

  1. 多步驟結果：如果需要計算每個旅遊路線的停靠站數量，然後再計算平均值，可以先使用子查詢計算每個路線的停靠站數量，再對結果進行平均值計算。
SELECT AVG(num_stops)
FROM (SELECT name, MAX(stop) AS num_stops
      FROM tour
      GROUP BY name) tour_stops;

內容解密：

  1. 內層查詢計算每個旅遊路線的最大停靠站數（num_stops）。

  2. 外層查詢對這些結果進行平均值計算。

  3. 處理大資料集：當連線操作涉及大資料表時，使用子查詢可以先過濾資料，減少參與連線的資料量，從而提高查詢效率。

與WITH子句的比較

除了在FROM子句中使用子查詢，還可以使用WITH子句來定義通用表表達式（CTE）。這兩種方法都可以達到簡化查詢的目的，但WITH子句在某些情況下更具優勢。

WITH子句範例

WITH o AS (SELECT * FROM owner WHERE type = 'public')
SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM o JOIN waterfall w ON o.id = w.owner_id;

內容解密：

  1. WITH o AS (...)定義了一個名為o的CTE，內容是篩選出type為'public'的擁有者。
  2. 主查詢使用這個CTE進行連線操作。

資料函式庫支援度

  • WITH子句在MySQL 8.0+、PostgreSQL、Oracle、SQL Server和SQLite中得到支援。
  • 子查詢在所有主要資料函式庫系統中均被廣泛支援。

WHERE子句與過濾資料

除了在FROM子句中使用子查詢，WHERE子句也是過濾資料的重要工具。它允許根據特定條件篩選結果集。

範例

SELECT id, name
FROM waterfall
WHERE name NOT LIKE '%Falls%';

內容解密：

  1. WHERE name NOT LIKE '%Falls%'過濾掉名稱中包含Falls的記錄。
  2. 結果集中只包含符合條件的記錄。

多重條件過濾

可以使用邏輯運算子（如AND、OR）組合多個條件，以實作更精確的資料過濾。

SELECT id, name
FROM waterfall
WHERE name NOT LIKE '%Falls%' AND owner_id IS NULL;

內容解密：

  1. 同時滿足兩個條件：名稱中不包含Falls且owner_id為空。
  2. 只有符合這兩個條件的記錄才會出現在結果集中。

SQL查詢基礎：WHERE、GROUP BY與資料過濾

在SQL查詢中，WHERE與GROUP BY是兩個非常重要的子句，分別用於資料過濾與資料分組彙總。本文將探討這兩個子句的使用方法及其在實際查詢中的應用。

使用WHERE進行資料過濾

WHERE子句用於篩選滿足特定條件的資料行。例如，若要查詢所有對公眾開放的瀑布，可以使用以下查詢：

SELECT w.name
FROM waterfall w
WHERE w.open_to_public = 'y';

若要進一步篩選出位於Alger郡的瀑布，可以使用子查詢或連線（JOIN）操作：

使用子查詢

SELECT w.name
FROM waterfall w
WHERE w.open_to_public = 'y'
AND w.county_id IN (
  SELECT c.id FROM county c
  WHERE c.name = 'Alger'
);

使用連線操作

SELECT w.name
FROM waterfall w INNER JOIN county c
ON w.county_id = c.id
WHERE w.open_to_public = 'y'
AND c.name = 'Alger';

這兩種方法都能得到相同的結果，但連線操作通常比子查詢更高效。

GROUP BY子句的使用

GROUP BY子句用於將資料行分組並對每組進行彙總計算。例如，若要統計每個旅遊路線上的瀑布數量，可以使用以下查詢：

SELECT t.name AS tour_name,
       COUNT(*) AS num_waterfalls
FROM waterfall w INNER JOIN tour t
ON w.id = t.stop
GROUP BY t.name;

內容解密：

  1. GROUP BY t.name 將資料行按照t.name（旅遊路線名稱）進行分組。
  2. COUNT(*) AS num_waterfalls 對每組資料行進行計數，統計每個旅遊路線上的瀑布數量。
  3. 連線（JOIN）操作將waterfall表與tour表關聯起來，以便取得每個瀑布對應的旅遊路線資訊。

其他資料過濾方法

除了WHERE子句，還可以在以下幾個地方進行資料過濾：

  • FROM子句中的連線條件：在連線多個表時，可以在ON子句中指定連線條件，同時進行資料過濾。
  • HAVING子句：當使用聚合函式時，可以在HAVING子句中指定過濾條件。
  • LIMIT子句：用於限制查詢結果傳回的資料行數，在不同資料函式庫系統中有不同的實作方式（如Oracle中的ROWNUM，SQL Server中的SELECT TOP）。

SQL查詢基礎：分組、過濾與排序

在SQL查詢中，GROUP BY、HAVING和ORDER BY是三個非常重要的子句，它們分別用於資料的分組、過濾和排序。

使用GROUP BY進行資料分組

當需要根據某些欄位對資料進行分組，並對每組資料進行聚合計算時，就需要使用GROUP BY子句。例如，假設我們有一個包含旅遊路線和停靠點的資料表，我們想要統計每個旅遊路線的停靠點數量。

SELECT t.name AS tour_name,
       COUNT(*) AS num_waterfalls
FROM waterfall w 
INNER JOIN tour t 
ON w.id = t.stop
GROUP BY t.name;

這個查詢會傳回每個旅遊路線的名稱和對應的停靠點數量。

內容解密：

  1. SELECT t.name AS tour_name, COUNT(*) AS num_waterfalls：選擇旅遊路線的名稱，並計算每個路線的停靠點數量。
  2. FROM waterfall w INNER JOIN tour t ON w.id = t.stop：將waterfall表和tour表根據停靠點ID進行內連線。
  3. GROUP BY t.name：根據旅遊路線的名稱對結果進行分組。

使用HAVING進行分組過濾

HAVING子句用於對GROUP BY後的結果進行過濾。例如，如果我們只想檢視停靠點數量為6的旅遊路線，可以這樣寫：

SELECT t.name AS tour_name,
       COUNT(*) AS num_waterfalls
FROM waterfall w 
INNER JOIN tour t 
ON w.id = t.stop
GROUP BY t.name
HAVING COUNT(*) = 6;

內容解密：

  1. GROUP BY t.name：根據旅遊路線名稱進行分組。
  2. HAVING COUNT(*) = 6：只選擇停靠點數量為6的旅遊路線。

WHERE與HAVING的比較

  • WHERE子句用於在資料分組前進行過濾，適用於非聚合條件。
  • HAVING子句用於在資料分組後進行過濾，適用於聚合條件。

使用ORDER BY進行結果排序

ORDER BY子句用於對查詢結果進行排序。例如，根據業主名稱和瀑布名稱對結果進行排序：

SELECT COALESCE(o.name, 'Unknown') AS owner,
       w.name AS waterfall_name
FROM waterfall w 
LEFT JOIN owner o 
ON w.owner_id = o.id
ORDER BY owner, waterfall_name;

內容解密：

  1. COALESCE(o.name, 'Unknown')：將NULL的業主名稱替換為’Unknown’。
  2. ORDER BY owner, waterfall_name：首先根據業主名稱排序，然後根據瀑布名稱排序。

使用LIMIT限制結果數量

在某些資料函式庫系統中（如MySQL、PostgreSQL和SQLite），可以使用LIMIT子句來限制查詢結果的數量。例如：

SELECT *
FROM owner
LIMIT 3;

這個查詢會傳回owner表的前3行資料。

內容解密：

  1. SELECT * FROM owner：選擇owner表的所有欄位。
  2. LIMIT 3：限制結果數量為3行。