QUERY 函式大解析(三):ORDER BY、LIMIT、OFFSET、LABEL

2021/07/25閱讀時間約 8 分鐘
我們在最近的系列文介紹了 QUERY 函式的用法,有 SELECT、WHERE 就可以應付不少的搜尋狀況。今天再介紹四個小功能:ORDER BY、LIMIT、OFFSET 和 LABEL 給大家,讓你的 QUERY 更強大!
我在方格子上寫了一系列使用 QUERY 的教學文章,也歡迎你來看看:

先來重點摘錄一下這四個功能:
  • ORDER BY:排序 QUERY 結果
  • LIMIT:限制回傳行數
  • OFFSET:跳過N行後執行 QUERY
  • LABEL:設定新的標題名稱

ORDER BY:排序 QUERY 結果

我們可以指定某個欄位為基準,對 QUERY 結果做遞增或遞減排序。語法很直接,就是:
=QUERY(..., "SELECT ....... ORDER BY 欄位字母")
例如我有個表格長這樣子:
我想要搜尋資料庫內所有女性的資料,並希望回傳結果是以身分證字號的字母(也就是B欄)為基準,做遞增(A→Z)的排序的話,就是:
=QUERY(A1:E10, "SELECT * WHERE C = '女' ORDER BY B", 1)
就會得到:
你會發現這邊不僅回傳所有女性(C欄都符合「女」)的資料,身分證字號也按照遞增順序排列整齊了(B→C→D→G→H)!
如果今天你想要做遞減的排序,只要在 ORDER BY 指令後指定好欄位後,再加個 DESC 就好,也就是:
=QUERY(..., "SELECT ....... ORDER BY 欄位字母 DESC)
用同樣的資料範圍,只把以身分證字號的首字順序遞減:
=QUERY(A1:E10, "SELECT * WHERE C = '女' ORDER BY B DESC", 1)
就會得到:
所有性別符合「女」的資料都回傳了,身分證字號也是遞減顯示(H→G→D→C→B)。就是這麼簡單!但有幾點要注意:
  1. ORDER BY 目前還不支援中文排序(如筆劃順序、注音順序或拼音順序)。
  2. ORDER BY 的預設排序就是遞增,你也可以加上 ASC 表示遞增:ORDER BY # ASC。
  3. 建議可以和 IS NOT NULL 一起並用,可以確保 QUERY 結果不會在最初數行回傳空值。
  4. 記得要把這指令放在 WHERE 之後。

LIMIT:限制回傳行數

有時我們不需要那麼多訊息,可能只要資料庫的一部分就好(例如只想收到 50 個結果),LIMIT 就會是我們的好夥伴。語法也很單純:
=QUERY(..., "SELECT ....... LIMIT 行數")
這邊的行數就填入數字就好了。例如:
  • LIMIT 5:只回傳 5 行結果。
  • LIMIT 10:只回傳 10 行結果。
  • LIMIT 20:只回傳 20 行結果。
同樣的,假設我們只想要前三筆的所有資訊,那語法就是:
=QUERY(A1:E10, "SELECT * LIMIT 3", 1)
你就會得到:
(不確定這邊的「標題」在說什麼的,可以右轉回去看一下:QUERY 函式大解析(一):基本原理與 SELECT 前面語法的部分!)
小提醒:
  1. LIMIT 後面只能接數字!
  2. 跟 ORDER BY 一起合用的話,可以得到「前」或「末」N筆的資料,這是一個很實用的連續技!使用語法是:=QUERY(..., "SELECT ....... ORDER BY 欄位 LIMIT 行數"),建議斟酌使用 DESC 來輔助你取得「前N筆」或「末N筆」的資料。

OFFSET:跳過N行後執行 QUERY

如題,輸入這個指令後就可以請 QUERY 跳過指定行數後,取得資料。直接上語法!
=QUERY(..., "SELECT ....... OFFSET 行數")
所以,如果你想:
  • 跳過 5 行開始取資料:=QUERY(..., "SELECT ....... OFFSET 5")
  • 跳過 10 行開始取資料:=QUERY(..., "SELECT ....... OFFSET 10")
  • 跳過 30 行開始取資料:=QUERY(..., "SELECT ....... OFFSET 30")
來看看同樣的資料庫:
今天如果要跳過 3 行,開始取得剩下的資料,語法就會是:
=QUERY(A1:E10, "SELECT * OFFSET 3", 1)
你就會得到:
稍稍對比,你會發現資料是從第 5 行開始取的,不包含標題、跳過了第 2、第 3、第 4 三行。
小提醒:
  1. OFFSET 也很常和 LIMIT 一起合用,得到從某行開始、特定數筆的資料。語法順序要稍稍注意,必須先寫 LIMIT 再寫 OFFSET。
    語法範例:
=QUERY(..., "SELECT ....... LIMIT 行數 OFFSET 行數")

LABEL:設定新的標題名稱

如果你必須在不更改原本資料庫的情況下,得重新設定新的標題並進行 QUERY,LABEL 就是個好選擇。語法如下:
=QUERY(..., "SELECT ....... LABEL 欄位字母 '想取的新名字'")
如果你想設定兩個以上的標題也當然是可以的,語法像是這樣:
=QUERY(..., "SELECT ....... 
LABEL 欄位字母甲 '想取的新名字甲',
欄位字母乙 '想取的新名字乙', ...")
一樣請出我們這次的資料庫:
假如今天得在 QUERY 結果要把「姓名」(A欄)改成「Name」的話,我們的語法會寫成:
=QUERY(A1:E10, "SELECT * LABEL A 'Name'", 1)
執行結果如下:
欸嘿!變成「Name」了!
那如果我要把全部的標題(名字、身分證字號、性別、手機號碼)都改成英文的話,就可以寫成:
=QUERY(A1:E10, "SELECT * LABEL A 'Name',
B 'ID',
C 'Gender',
D 'Mobile'", 1)
你就會得到:
OK,打完收工!
小提醒:
  1. 如果有兩個以上的標題要指定,別忘了在標題和標題之間放上逗號(,)隔開,標題字串也要用單引號(')。
  2. 你如果想讓標題空白,也可以指定「''」:LABEL A ''。

我非常鼓勵你打開你現有的資料,搭配 WHERE 交互使用,會有更多不同的發現!我個人很喜歡 WHERE、ORDER BY 和 LIMIT 的組合技,可以讓我在篩選一定條件下需要的資料筆數,還可以按照自己的需求做排序,會讓 QUERY 更有效率,快速取得資料。如果你在使用上遇到任何問題,也歡迎你在文章下面留言讓我知道,我會盡力幫忙!
如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!
我是喜特先生,Mr. Sheet,我們下個教學見!
3.2K會員
122內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
發表第一個留言支持創作者!