Chapter 13. 函式 (八)：QUERY › 13.6 其他資料篩選與設定
13.6 其他資料篩選與設定
本節將介紹其他與資料篩選及設定相關的語法，將先簡單介紹各個語法的功能、範例，並說明在所有 QUERY 的語法中使用的順序，相關語法共有以下七個：
語法 | 說明 |
limit | limit 數字 用來限制輸出的列數，例如 select * limit 30。 |
offset | offset 數字 跳過資料中指定數量的列數，例如 select * offset 30 (從第 31 列開始)。 |
skipping | skipping 數字 每隔幾列抽取一筆資料，例如 select * skipping 30 (第 1 / 31 / 61 /… 列)。 |
order by | order by 欄位 [desc / asc] [, 欄位...] 依據哪個欄位的值排序，後面可加上 desc / asc 表示遞減 / 遞增排序 ，預設為 asc，例如 select * order by A asc, B asc, C desc。 |
label | label 欄位 '名稱' [, 欄位 '名稱'...] 將某欄位的表頭名稱改成指定的名稱，例如 select A, sum(B) group by A label A '項目', sum(B) '加總'。 |
format | format 欄位 '內容格式' [, 欄位 '內容格式'] 將欄位的輸出改成指定格式，例如 select * format A '#,###.00', B 'yyyy-MM-dd', C 'Yes:No'，有以下三種格式：
雖然以上三種方式可以設定輸出的格式，但若輸出的儲存格本身有格式設定時，會以儲存格的格式為主。 |
options | options no_format / no_values
|
到了這裡，我們已經介紹了所有 QUERY 相關的語法，而在使用這些語法時實際上需要有固定的呈現順序，如下：
select … where … group by … pivot … order by … skipping … limit … offset … label … format … options …
以下將舉幾個例子說明上述語法的用法：
- 顯示第 4~8 位會員的卡號
公式
=QUERY(會員資料,"select A limit 5 offset 3")
解說
使用 offset 3 跳過 3 筆資料，並 limit 5 使用輸出五筆資料。
- 顯示第 3 / 5 / 7 / 9 / 11 位會員的卡號
公式
=QUERY(會員資料,"select A skipping 2 limit 5 offset 1")
解說
skipping 與 offset 一起使用時，會先使用 skipping 2 輸出第 1 / 3 / 5 / 7… 筆資料，再使用 offset 1 跳過上述的第一筆資料，因此會輸出 3 / 5 / 7 … 筆資料。
- 顯示金額最大的 10 筆交易編號及其金額，將金額欄位取名為「交易金額」，格式使用 $X,XXX
公式
=QUERY(交易記錄,"select A,sum(K) group by A order by sum(K) desc limit 10 label sum(K) '交易金額' format sum(K) '$#,#'")
解說
- 使用 select A,sum(K) group by A 輸出每位會員卡號的貨品金額加總。
- 使用 order by sum(K) desc limit 10 讓資料以總金額遞減排序，並輸出金額前十多的會員。
- 使用 label sum(K) '交易金額' format sum(K) '$#,#' 重新命名表頭與設定資料的格式。
到這裡已經介紹完所有 QUERY 相關的語法了，也許你會認為 QUERY 本身沒有很實用，但其實若將 QUERY 與儲存格的輸入並用，也就是在 查詢 中使用儲存格的值，會大幅增加此函式的實用性，而做法也相當簡單，只要在字串中使用 & 連接儲存格的內容即可，例如 "select A where A > "&A1&"。而在下一節的進階應用中，將會示範多層 QUERY 的用法，及如何將 QUERY 與其他函式搭配使用。