排序分組與限制資料
如果您想要跟隨範例，請參閱執行查詢的範例資料以檢視範例資料，並使用命令檔載入要測試的範例資料。命令檔會建立範例中使用的表格，並將資料載入表格中。
如果您想要跟隨範例，請參閱執行查詢的範例資料以檢視範例資料，並瞭解如何使用 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 值來限制要傳回的列數目。建議搭配 ORDER BY 子句使用 LIMIT 和 OFFSET。否則，結果會以隨機順序傳回，產生不可預測的結果。
使用 LIMIT 和 OFFSET 的良好使用案例 / 範例是結果的應用程式分頁。例如，假設您的應用程式想要每頁顯示 4 筆結果。您可以使用限制和偏移來在應用程式中實行無狀態分頁。如果每頁顯示 n (表示 4) 個結果，則顯示頁面 m (例如 2) 的結果，則此範例中的偏移量為 (n*m-1)，而限制為 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 表格中的資料，然後根據上次看到的時間排序結果。您可以使用未巢狀陣列來均一化資料。也就是將 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 表格中的資料，然後根據上次看到的時間排序結果。您可以使用未巢狀陣列來均一化資料。您必須顯示第二頁的內容，以設定 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 的列。然後使用 count 函數來判斷結果資料列的包裝袋數目。
您可以在此比較 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() 方法。
請從此處的範例下載完整代碼 GroupSortData.py 。
# 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 函數。
從此處的範例下載完整代碼 GroupSortData.go 。
//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：從此處的範例下載完整程式碼 GroupSortData.js 。
//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：從此處的範例下載完整的代碼 GroupSortData.ts 。
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 方法，然後重複產生的非同步列舉項目。
從此處的範例下載完整代碼 GroupSortData.cs。
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);