排序分組與限制資料
如果您想要跟隨範例，請參閱 以檢視範例資料，並使用指令碼載入測試的範例資料。命令檔會建立範例中使用的表格，並將資料載入表格中。
如果您想要跟隨範例，請參閱執行查詢的範例資料以檢視範例資料，並瞭解如何使用 OCI 主控台建立範例表格，並使用 JSON 檔案載入資料。
排序結果
使用 ORDER BY 子句依任一資料欄、主索引鍵或非主索引鍵排序結果。
範例 1：依所有乘客的全名排序機票號碼。
SELECT bag.ticketNo, bag.fullName
FROM BaggageInfo bag
ORDER BY bag.fullName說明：您正在根據乘客的全名，以遞增順序在 BaggageInfo 綱要中排序乘客的機票號碼。
輸出：
{"ticketNo":1762344493810,"fullName":"Adam Phillips"}
{"ticketNo":1762392135540,"fullName":"Adelaide Willard"}
{"ticketNo":1762376407826,"fullName":"Dierdre Amador"}
{"ticketNo":1762355527825,"fullName":"Doris Martin"}
{"ticketNo":1762324912391,"fullName":"Elane Lemons"}
{"ticketNo":1762350390409,"fullName":"Fallon Clements"}
{"ticketNo":1762341772625,"fullName":"Gerard Greene"}
{"ticketNo":176234463813,"fullName":"Henry Jenkins"}
{"ticketNo":1762383911861,"fullName":"Joanne Diaz"}
{"ticketNo":1762377974281,"fullName":"Kendal Biddle"}
{"ticketNo":1762355854464,"fullName":"Lisbeth Wampler"}
{"ticketNo":1762320369957,"fullName":"Lorenzo Phil"}
{"ticketNo":1762320569757,"fullName":"Lucinda Beckman"}
{"ticketNo":1762340683564,"fullName":"Mary Watson"}
{"ticketNo":1762330498104,"fullName":"Michelle Payne"}
{"ticketNo":1762348904343,"fullName":"Omar Harvey"}
{"ticketNo":1762399766476,"fullName":"Raymond Griffin"}
{"ticketNo":1762311547917,"fullName":"Rosalia Triplett"}
{"ticketNo":1762357254392,"fullName":"Teena Colley"}
{"ticketNo":1762390789239,"fullName":"Zina Christenson"}
{"ticketNo":1762340579411,"fullName":"Zulema Martindale"}範例 2：擷取上次看到之工作站為 MEL 之乘客 (依姓名排序) 的上次看到時間 (最晚的優先) 的乘客詳細資訊 (全名、標記號碼)。
SELECT bag.fullName, bag.bagInfo[].tagNum,
bag.bagInfo[].lastSeenTimeGmt
FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any "MEL"
ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC說明：您首先根據上次看到的工作站篩選
BaggageInfo 表格中的資料，然後根據上次看到的時間和乘客的全名，以遞減順序排序篩選的結果。您可以使用 ORDER BY 子句來執行此操作。
附註：
您可以使用多個資料欄來排序查詢的輸出。輸出：
{"fullName":"Adam Phillips","tagNum":"17657806255240","lastSeenTimeGmt":"2019-02-01T16:13:00Z"}
{"fullName":"Zina Christenson","tagNum":"17657806228676","lastSeenTimeGmt":"2019-02-04T10:08:00Z"}
{"fullName":"Joanne Diaz","tagNum":"17657806292518","lastSeenTimeGmt":"2019-02-16T16:13:00Z"}
{"fullName":"Zulema Martindale","tagNum":"17657806288937","lastSeenTimeGmt":"2019-02-25T20:15:00Z"}限制和位移結果
使用 LIMIT 子句來限制從 SELECT 敘述句傳回的結果數目。例如，如果表格中有 1000 個資料列，請指定 LIMIT 值來限制要傳回的資料列數目。建議將 LIMIT 和 OFFSET 與 ORDER BY 子句搭配使用。否則，結果會以隨機順序傳回，產生無法預測的結果。
使用 LIMIT 和 OFFSET 的良好使用案例 / 範例是結果的應用程式分頁。例如，您的應用程式想要每頁顯示 4 筆結果。您可以使用限制和位移，在應用程式中實行無狀態分頁。如果每頁顯示 n 個結果 (例如 4 個)，則顯示 m 頁的結果 (例如 2)，則偏移為 (n*m-1)，在此範例中為 4，而限制為 n (此處為 4 個)。
範例 1：您的應用程式可在頁面上顯示 4 個結果。在第一頁擷取您應用程式所擷取的詳細資料，供上次看到工作站為 JTR 的乘客使用。
SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time
FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
ORDER BY $flt_time LIMIT 4說明：您可以根據上次看到的工作站篩選 BaggageInfo 表格中的資料，然後根據上次看到的時間來排序結果。您可以使用 unnest 陣列將資料均一化。亦即，bagInfo 陣列會均一化並擷取上次看到的時間。您只需要顯示結果集的前 4 個資料列。
輸出：
{"fullName":"Michelle Payne","tagNum":"17657806247861","flt_time":"2019-02-02T23:59:00Z"}
{"fullName":"Gerard Greene","tagNum":"1765780626568","flt_time":"2019-03-07T16:01:00Z"}
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"flt_time":"2019-03-12T15:05:00Z"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","flt_time":"2019-03-12T15:05:00Z"}範例 2：您的應用程式可在頁面上顯示 4 個結果。在第二頁擷取您應用程式所擷取的詳細資料，供上次看到的站台為 JTR 的乘客使用。
SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time
FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
ORDER BY $flt_time LIMIT 4 OFFSET 4說明：您可以根據上次看到的工作站篩選 BaggageInfo 表格中的資料，然後根據上次看到的時間來排序結果。您可以使用 unnest 陣列將資料均一化。您必須顯示第二頁的內容，才能設定 OFFSET 4。雖然您限制為 4 個資料列，但只有一個資料列顯示，因為總結果集只有 5 個。會略過前幾個項目，並顯示第五個項目。
輸出：
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"flt_time":"2019-03-12T16:05:00Z"}分組結果
使用 GROUP BY 子句依一或多個表格資料欄將結果分組。一般而言，GROUP BY 子句會與聚總表示式 (例如 COUNT、SUM 和 AVG) 一起使用。
範例 1：顯示每個預訂的行李數目。
SELECT bag.confNo,
count(bag.bagInfo) AS TOTAL_BAGS
FROM BaggageInfo bag
GROUP BY bag.confNo說明：每個乘客都有一個預訂代碼 (confNo)。乘客可攜帶多個行李。在這裡您可以根據預訂代碼來分組資料，並得到 bagInfo 陣列的數目，提供每筆預訂的行李數目。
輸出：
{"confNo":"FH7G1W","TOTAL_BAGS":1}
{"confNo":"PQ1M8N","TOTAL_BAGS":1}
{"confNo":"XT6K7M","TOTAL_BAGS":1}
{"confNo":"DN3I4Q","TOTAL_BAGS":1}
{"confNo":"QB1O0J","TOTAL_BAGS":1}
{"confNo":"TX1P7E","TOTAL_BAGS":1}
{"confNo":"CG6O1M","TOTAL_BAGS":1}
{"confNo":"OH2F8U","TOTAL_BAGS":1}
{"confNo":"BO5G3H","TOTAL_BAGS":1}
{"confNo":"ZG8Z5N","TOTAL_BAGS":1}
{"confNo":"LE6J4Z","TOTAL_BAGS":1}
{"confNo":"XT1O7T","TOTAL_BAGS":1}
{"confNo":"QI3V6Q","TOTAL_BAGS":2}
{"confNo":"RL3J4Q","TOTAL_BAGS":1}
{"confNo":"HJ4J4P","TOTAL_BAGS":1}
{"confNo":"CR2C8MY","TOTAL_BAGS":1}
{"confNo":"LN0C8R","TOTAL_BAGS":1}
{"confNo":"MZ2S5R","TOTAL_BAGS":1}
{"confNo":"KN4D1L","TOTAL_BAGS":1}
{"confNo":"MC0E7R","TOTAL_BAGS":1}範例 2：選取源自每個機場的行李總計 (不含運輸行李)。
SELECT $flt_src as SOURCE,
count(*) as COUNT
FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src
GROUP BY $flt_src說明：您要取得源自每個機場的行李總數。不過，您不想考量運輸中一部分的機場。因此，您會將資料與 flightLegs 陣列第一筆記錄的飛行來源值進行分組 (因為第一筆記錄是來源)。然後決定行李數目。
輸出：
{"SOURCE":"SFO","COUNT":6}
{"SOURCE":"BZN","COUNT":1}
{"SOURCE":"GRU","COUNT":1}
{"SOURCE":"LAX","COUNT":1}
{"SOURCE":"YYZ","COUNT":1}
{"SOURCE":"MEL","COUNT":1}
{"SOURCE":"MIA","COUNT":4}
{"SOURCE":"MSQ","COUNT":2}
{"SOURCE":"MXP","COUNT":2}
{"SOURCE":"JFK","COUNT":3}聚總結果
使用內建的聚總和序列聚總函數來尋找計數、總和、平均值、最小值或最大值等資訊。
範例 1：尋找估計會在特定時間到達 LAX 機場的已檢查袋總數。
SELECT $estdate as ARRIVALDATE,
count($flight) AS COUNT
FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs.estimatedArrival $estdate,
$bag.bagInfo.flightLegs.flightNo $flight,
$bag.bagInfo.flightLegs.fltRouteDest $flt_dest
WHERE $estdate =any "2019-02-01T11:00:00Z" AND $flt_dest =any "LAX"
GROUP BY $estdate說明：在航空公司行李追蹤應用程式中，您可以取得預估抵達特定機場與時間的托運行李總數。對於每個航班航段，BaggageInfo 表格之 flightLegs 陣列中的 estimatedArrival 欄位包含已檢查行李的抵達時間，而 fltRouteDest 欄位則包含目的地機場代碼。在上述查詢中，若要確定在指定時間到達 LAX 機場的已檢查行李總數，您必須先使用 GROUP BY 子句將資料與預估抵達時間值分組。在群組中，您只選取目的地機場為 LAX 的列。然後，您可以使用計數函數來決定結果資料列的行數。
在這裡，您可以比較字串格式化的日期 (格式為 ISO-8601)，這是因為字串的自然排序順序所造成，而不需要將它們轉換成時戳資料類型。$bag.bagInfo.flightLegs.estimatedArrival 和 $bag.bagInfo.flightLegs.fltRouteDest 是序列。因為比較表示式 '=' 無法在多個項目的順序上作業，所以會使用順序比較運算子 '=any' 來比較 estimatedArrival 與 fltRouteDest 欄位。
輸出：
{"ARRIVALDATE":"2019-02-01T11:00:00Z","COUNT":2}範例 2：在航空公司行李追蹤應用程式中，顯示與托運行李數目、差旅路線及班機數目相關的自動訊息。
SELECT fullName,
b.baginfo[0].routing,
size(baginfo) AS BAGS,
CASE
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 1
THEN "You have one flight to catch"
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 2
THEN "You have two flights to catch"
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 3
THEN "You have three flights to catch"
ELSE "You do not have any travel listed today"
END AS FlightInfo
FROM BaggageInfo b
WHERE ticketNo = 1762320369957說明：在航空公司行李追蹤應用程式中，顯示關於乘客即將到來之差旅的航班計數、託運行李數目及遞送明細的快速查詢訊息非常有用。bagInfo 陣列包含乘客的託運袋明細。bagInfo 陣列的大小決定每位乘客的託運行李數目。bagInfo 中的 flightLegs 陣列包含對應至每個差旅航段的航班詳細資料。遞送欄位包含所有差旅片段的機場代碼。您可以計算 flightLegs 陣列中的 flightNo 欄位來決定航班數目。如果乘客有多個託運袋，bagInfo 陣列中會有一個以上的元素，每個袋中會有一個。在此情況下，乘客資料之 bagInfo 欄位的所有元素中的 flightLegs 陣列將包含相同的值。這是因為乘客所有已入庫行李的目的地相同。計算 flightNo 欄位時，您必須只考慮 bagInfo 陣列的一個元素，以避免重複的結果。在此查詢中，您只考慮第一個元素，即 bagInfo[0]。由於 flightLegs 陣列具有每個差旅片段的 flightNo 欄位，因此它是一個序列，您可以使用 seq_count 函數來決定每個乘客的 flightNo 欄位計數。
您可以使用 CASE 陳述式，根據航班計數引入不同的訊息。為了方便使用，查詢中只會考量三個傳輸。
輸出：
{"fullName":"Lorenzo Phil","routing":"SFO/IST/ATH/JTR","BAGS":2,"FlightInfo":"You have three flights to catch"}使用 QueryRequest API 的範例
您可以使用 QueryRequest API 將資料分組並排序，也可以從 NoSQL 表格擷取資料。
若要執行查詢，請使用 NoSQLHandle.query() API。
Download the full code GroupSortData.java from the examples here.
//Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
} String orderby_stmt="SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag "+
"WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
System.out.println("Using ORDER BY to sort data ");
fetchRows(handle,orderby_stmt);
String sortlimit_stmt="SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag, "+
"$bag.bagInfo[].lastSeenTimeGmt $flt_time WHERE $bag.bagInfo[].lastSeenStation=any \"JTR\""+
"ORDER BY $flt_time LIMIT 4";
System.out.println("Using ORDER BY and LIMIT to sort and limit data ");
fetchRows(handle,sortlimit_stmt);
String groupsortlimit_stmt="SELECT $flt_src as SOURCE,count(*) as COUNT FROM BaggageInfo $bag, "+
"$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src";
System.out.println("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data ");
fetchRows(handle,groupsortlimit_stmt); 若要執行查詢，請使用 borneo.NoSQLHandle.query() 方法。
Download the full code GroupSortData.py from the examples here.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
print('Query results for: ' + sqlstmt)
result = handle.query(request)
for r in result.get_results():
print('\t' + str(r)) orderby_stmt = '''SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC'''
print('Using ORDER BY to sort data:')
fetch_data(handle,orderby_stmt)
sortlimit_stmt = '''SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
ORDER BY $flt_time LIMIT 4'''
print('Using ORDER BY and LIMIT to sort and limit data:')
fetch_data(handle,sortlimit_stmt)
groupsortlimit_stmt = '''SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src'''
print('Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data:')
fetch_data(handle,groupsortlimit_stmt) 若要執行查詢，請使用 Client.Query 函數。
Download the full code GroupSortData.go from the examples here.
//fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
} orderby_stmt := `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any "MEL" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
fmt.Printf("Using ORDER BY to sort data::\n")
fetchData(client, err,tableName,orderby_stmt)
sortlimit_stmt := `SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
ORDER BY $flt_time LIMIT 4`
fmt.Printf("Using ORDER BY and LIMIT to sort and limit data::\n")
fetchData(client, err,tableName,sortlimit_stmt)
groupsortlimit_stmt := `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
fmt.Printf("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data::\n")
fetchData(client, err,tableName,groupsortlimit_stmt) 若要執行查詢，請使用 query 方法。
JavaScript: Download the full code GroupSortData.js from the examples here.
//fetches data from the table
async function fetchData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}TypeScript: Download the full code GroupSortData.ts from the examples here.
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
const orderby_stmt = `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
console.log("Using ORDER BY to sort data");
await fetchData(handle,orderby_stmt);
const sortlimit_stmt = `SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR"
ORDER BY $flt_time LIMIT 4`
console.log("Using ORDER BY and LIMIT to sort and limit data");
await fetchData(handle,sortlimit_stmt);
const groupsortlimit_stmt = `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
console.log("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data");
await fetchData(handle,groupsortlimit_stmt); 若要執行查詢，您可以呼叫 QueryAsync 方法或呼叫 GetQueryAsyncEnumerable 方法，然後重複產生的非同步可列舉。
Download the full code GroupSortData.cs from the examples here.
private static async Task fetchData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
} private const string orderby_stmt =@"SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt
FROM BaggageInfo bag WHERE bag.bagInfo[].lastSeenStation=any ""MEL""
ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
Console.WriteLine("\nUsing ORDER BY to sort data!");
await fetchData(client,orderby_stmt);
private const string sortlimit_stmt =@"SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time FROM BaggageInfo $bag,
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any ""JTR""
ORDER BY $flt_time LIMIT 4";
Console.WriteLine("\nUsing ORDER BY and LIMIT to sort and limit data!");
await fetchData(client,sortlimit_stmt);
private const string groupsortlimit_stmt =@"SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src" ;
Console.WriteLine("\nUsing GROUP BY, ORDER BY and LIMIT to group, sort and limit data:");
await fetchData(client,groupsortlimit_stmt);