QUERY 函式大解析（十四）：子查詢①篩選聚集結果
這邊有一張表格，有員工的姓名、部門、年齡、薪水、公司的資料：
在這個表格裡，我想找「薪水在高於平均值的員工」有哪些，可以怎麼找呢？或許你有閃過這樣的寫法：
=QUERY(A:E, "SELECT * WHERE D > AVG(D)")
但是這樣會回傳錯誤，因為 AVG(D) 這樣的聚集函數沒辦法出現在 WHERE 語句裡面。
那還可以怎麼寫呢？如果是在 SQL 上，你可以用 HAVING 來完成，用它對聚集函數的結果進行篩選。可惜 QUERY 沒有 HAVING 這個方法，那還可以怎麼解決呢？
我想要介紹一個新東西，叫做子查詢（Subquery）。
子查詢（Subquery）
以 Google 試算表的 QUERY 角度來看，子查詢就是 QUERY 裡面再包著一個或多個 QUERY。我們通常會把開頭的等號（=）後的 QUERY 叫做主查詢，在 QUERY 裡面存在的 QUERY 叫做子查詢。
我們可以用子查詢來篩選、計算聚集（加總、平均、數量等）、從其他表格查詢資料來幫助現在的 QUERY，解鎖更多查詢的可能性！
在 QUERY，我們通常會把子查詢放在兩個地方。
- 在
WHERE中使用（第四行～第五行）：
=QUERY(資料,
"SELECT ...
WHERE A > "
& QUERY(資料,
"QUERY 語句") &
"")
當你想要以子查詢的結果作為 WHERE 的條件，就用這個！
要注意這種子查詢會用到 & 來連結，讓 WHERE 跟第二個 QUERY 的結果接在一起。你可以回到這邊複習一下 & 的用法：QUERY 函式大解析（九）：與儲存格連動。
- 當作主查詢的表格使用（第二行～第五行）：
=QUERY(
QUERY(A:D,
"SELECT ...
WHERE ...
..."),
"SELECT Col1, Col2, Col3 ...
WHERE ...
...")
當你想要先用子查詢做一個表格給主查詢用，就用這個！
這種子查詢則是不需要用 &，但是主查詢的時候要用 Col# 取代欄位字母，像是第一欄就是 Col1、第二欄就是 Col2、第三欄就是 Col3，以此類推。
不過這邊提到的使用時機只是一個參考，你可以看看自己的需求，再衡量要用哪一個方法來做子查詢。卡住的話也歡迎問喜特先生喔✨
對聚集結果篩選
好，回到一開始提到的題目。歡迎來這邊複製一份試算表，一起來練習！
在這個表格裡，我想找「薪水在高於平均值的員工」有哪些：
這邊我想用第一個方法（把子查詢放在 WHERE 後面）比較符合我的需求。我目前想到的是：
=QUERY('員工資料'!A:E,
"SELECT *
WHERE D >= 平均值")
（寫 QUERY 的時候，可以善用 Ctrl/Cmd + Enter 來換行喔）
這樣應該就可以得到答案了。不過這邊的「平均值」要怎麼求？我們可以先在「子查詢」的工作表，用 QUERY 求平均值，也就是：
=QUERY('員工資料'!A:E,
"SELECT AVG(D)")
來看看結果：
對，雖然得到數字了，不過第一列有個標題列，如果直接拿這個結果去跟主查詢接在一起的話，會產生錯誤，因為主查詢的 QUERY 會變成這樣：
=QUERY('員工資料'!A:E,
"SELECT *
WHERE D >= avg 薪水 65918.36735")
「avg 薪水 65918.36735」，文字跟數字混在一起了。
那怎麼把標題列拿掉？很簡單，我們可以再用 LABEL 語句，強制把「avg 薪水」變成空白。回到我們在工作表上寫的子查詢 QUERY，稍稍更改一下：
=QUERY('員工資料'!A:E,
"SELECT AVG(D)
LABEL AVG(D) ''"
讚，標題列不見啦！
再來把它跟主查詢用 & 接起來就可以了。
=QUERY('員工資料'!A:E,
"SELECT *
WHERE D >= "
& QUERY('員工資料'!A:E,
"SELECT AVG(D)
LABEL AVG(D) ''")
& "")
當然，你也可以繼續編輯主查詢。
比如說，我做了一個簡易的資料透視表，找到各個公司裡，各個部門超過平均值薪水的員工個數：
=QUERY('員工資料'!A:E,
"SELECT B, COUNT(A)
WHERE D >= "
& QUERY('員工資料'!A:E,
"SELECT AVG(D)
LABEL AVG(D) ''")
& "GROUP BY B
PIVOT E")
替代方案
如果你覺得子查詢有點複雜不好用，你也可以先在別的儲存格，先用 AVERAGE 函式算 D 欄的平均值是多少，然後再用 & 連接起來也是一招。我在「替代方案」工作表也做了一個範例，供你參考：
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
想要看更多文章的話，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
