SQL 複雜查詢

黃子毅發表於2022-03-14
SQL

SQL 複雜查詢指的就是子查詢。

為什麼子查詢叫做複雜查詢呢？因為子查詢相當於查詢巢狀查詢，因為巢狀導致複雜度幾乎可以被無限放大（無限巢狀），因此叫複雜查詢。下面是一個最簡單的子查詢例子：

SELECT pv FROM (
  SELECT pv FROM test
)

上面的例子等價於 SELECT pv FROM test，但因為把表的位置替換成了一個新查詢，所以搖身一變成為了複雜查詢！所以複雜查詢不一定真的複雜，甚至可能寫出和普通查詢等價的複雜查詢，要避免這種無意義的行為。

我們也要藉此機會瞭解為什麼子查詢可以這麼做。

理解查詢的本質

當我們查一張表時，資料庫認為我們在查什麼？

這點很重要，因為下面兩個語句都是合法的：

SELECT pv FROM test

SELECT pv FROM (
  SELECT pv FROM test
)

為什麼資料庫可以把子查詢當作表呢？為了統一理解這些概念，我們有必要對查詢內容進行抽象理解：任意查詢位置都是一條或多條記錄。

比如 test 這張表，顯然是多條記錄（當然只有一行就是一條記錄），而 SELECT pv FROM test 也是多條記錄，然而因為 FROM 後面可以查詢任意條數的記錄，所以這兩種語法都支援。

不僅是 FROM 可以跟單條或多條記錄，甚至 SELECT、GROUP BY、WHERE、HAVING 後都可以跟多條記錄，這個後面再說。

說到這，也就很好理解子查詢的變種了，比如我們可以在子查詢內使用 WHERE 或 GROUP BY 等等，因為無論如何，只要查詢結果是多條記錄就行了：

SELECT sum(people) as allPeople, sum(gdp), city FROM (
  SELECT people, gdp, city FROM test
  GROUP BY city
  HAVING sum(gdp) > 10000
)

這個例子就有點業務含義了。子查詢是從內而外執行的，因此我們先看內部的邏輯：按照城市分組，篩選出總 GDP 超過一萬的所有地區的人口數量明細。外層查詢再把人口數加總，這樣就能對比每個 GDP 超過一萬的地區，總人口和總 GDP 分別是多少，方便對這些重點城市做對比。

不過這個例子看起來還是不太自然，因為我們沒必要寫成複雜查詢，其實簡單查詢也是等價的：

SELECT sum(people) as allPeople, sum(gdp), city FROM test
GROUP BY city
HAVING sum(gdp) > 10000

那為什麼要多此一舉呢？因為複雜查詢的真正用法並不在這裡。

檢視

正因為子查詢的存在，我們才可能以類似抽取變數的方式，抽取子查詢，這個抽取出來的抽象就是檢視：

CREATE VIEW my_table(people, gdp, city)
AS
SELECT sum(people) as allPeople, sum(gdp), city FROM test
GROUP BY city
HAVING sum(gdp) > 10000

SELECT sum(people) as allPeople, sum(gdp), city FROM my_table

這樣的好處是，這個檢視可以被多條 SQL 語句複用，不僅可維護性變好了，執行時也僅需查詢一次。

要注意的是，SELECT 可以使用任何檢視，但 INSERT、DELETE、UPDATE 用於檢視時，需要檢視滿足一下條件：

  1. 未使用 DISTINCT 去重。
  2. FROM 單表。
  3. 未使用 GROUP BY 和 HAVING。

因為上面幾種模式都會導致檢視成為聚合後的資料，不方便做除了查以外的操作。

另外一個知識點就是物化檢視，即使用 MATERIALIZED 描述檢視：

CREATE MATERIALIZED VIEW my_table(people, gdp, city)
AS ...

這種檢視會落盤，為什麼要支援這個特性呢？因為普通檢視作為臨時表，無法利用索引等優化手段，查詢效能較低，所以物化檢視是較為常見的效能優化手段。

說到效能優化手段，還有一些比較常見的理念，即把讀的複雜度分攤到寫的時候，比如提前聚合新表落盤或者對 CASE 語句固化為欄位等，這裡先不展開。

標量子查詢

上面說了，WHERE 也可以跟子查詢，比如：

SELECT city FROM test
WHERE gdp > (
  SELECT avg(gdp) from test
)

這樣可以查詢出 gdp 大於平均值的城市。

那為什麼不能直接這麼寫呢？

SELECT city FROM test
WHERE gdp > avg(gdp) -- 報錯，WHERE 無法使用聚合函式

看上去很美好，但其實第一篇我們就介紹了，WHERE 不能跟聚合查詢，因為這樣會把整個父查詢都聚合起來。那為什麼子查詢可以？因為子查詢聚合的是子查詢啊，父查詢並沒有被聚合，所以這才符合我們的意圖。

所以上面例子不合適的地方在於，直接在當前查詢使用 avg(gdp) 會導致聚合，而我們並不想聚合當前查詢，但又要通過聚合拿到平均 GDP，所以就要使用子查詢了！

回過頭來看，為什麼這一節叫標量子查詢？標量即單一值，因為 avg(gdp) 聚合出來的只有一個值，所以 WHERE 可以把它當做一個單一數值使用。反之，如果子查詢沒有使用聚合函式，或 GROUP BY 分組，那麼就不能使用 WHERE > 這種語法，但可以使用 WHERE IN，這涉及到單條與多條記錄的思考，我們接著看下一節。

單條和多條記錄

介紹標量子查詢時說到了，WHERE > 的值必須時單一值。但其實 WHERE 也可以跟返回多條記錄的子查詢結果，只要使用合理的條件語句，比如 IN：

SELECT area FROM test
WHERE gdp IN (
  SELECT max(gdp) from test
  GROUP BY city
)

上面的例子，子查詢按照城市分組，並找到每一組 GDP 最大的那條記錄，所以如果資料粒度是區域，那麼我們就查到了每個城市 GDP 最大的那些記錄，然後父查詢通過 WHERE IN 找到 gdp 符合的複數結果，所以最後就把每個城市最大 gdp 的區域列了出來。

但實際上 WHERE > 語句跟複數查詢結果也不會報錯，但沒有任何意義，所以我們要理解查詢結果是單條還是多條，在 WHERE 判斷時選擇合適的條件。WHERE 適合跟複數查詢結果的語法有：WHERE IN、WHERE SOME、WHERE ANY。

關聯子查詢

所謂關聯子查詢，即父子查詢間存在關聯，既然如此，子查詢肯定不能單獨優先執行，畢竟和父查詢存在關聯嘛，所以關聯子查詢是先執行外層查詢，再執行內層查詢的。要注意的是，對每一行父查詢，子查詢都會執行一次，因此效能不高（當然 SQL 會對相同引數的子查詢結果做快取）。

那這個關聯是什麼呢？關聯的是每一行父查詢時，對子查詢執行的條件。這麼說可能有點繞，舉個例子：

SELECT * FROM test where gdp > (
  select avg(gdp) from test
  group by city
)

對這個例子來說，想要查詢 gdp 大於按城市分組的平均 gdp，比如北京地區按北京比較，上海地區按上海比較。但很可惜這樣做是不行的，因為父子查詢沒有關聯，SQL 並不知道要按照相同城市比較，因此只要加一個 WHERE 條件，就變成關聯子查詢了：

SELECT * FROM test as t1 where gdp > (
  select avg(gdp) from test as t2 where t1.city = t2.city
  group by city
)

就是在每次判斷 WHERE gdp > 條件時，重新計運算元查詢結果，將平均值限定在相同的城市，這樣就符合需求了。

總結

學會靈活運用父子查詢，就掌握了複雜查詢了。

SQL 第一公民是集合，所以所謂父子查詢就是父子集合的靈活組合，這些集合可以出現在幾乎任何位置，根據集合的數量、是否聚合、關聯條件，就派生出了標量查詢、關聯子查詢。

更深入的瞭解就需要大量實戰案例了，但萬變不離其宗，掌握了複雜查詢後，就可以理解大部分 SQL 案例了。

討論地址是：精讀《SQL 複雜查詢》· Issue #403 · ascoders/weekly

如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。

關注 前端精讀微信公眾號

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）

相關文章