使用左外部結合與父項 - 子項表格
JOIN 可用來結合兩個或更多表格的資料列 (根據它們之間的相關資料欄)。在階層表格中，子項表格會繼承其父項表格的主索引鍵資料欄。這會以隱含方式完成，但不包括子項之 CREATE TABLE 敘述句中的父項資料欄。階層中的所有表格都有相同的分區索引鍵資料欄。
「左外部結合 (LOJ)」是其中一個結合作業，可讓您指定結合子句。
左外部結合簡介
「左外部結合 (LOJ)」是其中一個結合作業，可讓您指定結合子句。它會保留第一個 (左) 表格中不相符的資料列，在第二個 (右) 表格中以空值資料列結合它們。這表示右側表格中沒有相符資料列的所有左側資料列都會顯示在結果中，與空值配對以取代右側資料列。
在 LOJ 中，結果集中的欄位順序一律由上而下。這表示結果集中的輸出順序一律來自祖系表格，然後是子系表格。不論結合的順序為何，此為真。
LEFT OUTER JOIN 的特性：
- 查詢相同階層中的多個表格
- 這是 ANSI-SQL 標準
- 不支援同層級表格結合
您可以建立範例中使用的表格，並使用 OCI 主控台將資料載入表格。從 OCI 主控台，使用進階 DDL 輸入模式，使用下方提供的 DDL 敘述句建立下列表格。
### CREATE table ticket if not present ###
CREATE TABLE IF NOT EXISTS ticket(ticketNo LONG,
confNo STRING,
PRIMARY KEY(ticketNo));
### CREATE table ticket.baginfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo(id LONG,tagNum LONG,routing STRING,
lastActionCode STRING,
lastActionDesc STRING,
lastSeenStation STRING,
lastSeenTimeGmt TIMESTAMP(4),
bagArrivalDate TIMESTAMP(4), PRIMARY KEY(id));
### CREATE table ticket.bagInfo.flightLegs if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo.flightLegs(flightNo STRING,
flightDate TIMESTAMP(4),
fltRouteSrc STRING,
fltRouteDest STRING,
estimatedArrival TIMESTAMP(4),
actions JSON, PRIMARY KEY(flightNo));
### CREATE table ticket.passengerInfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.passengerInfo(contactPhone STRING,
fullName STRING,
gender STRING, PRIMARY KEY(contactPhone));請參閱建立單一表格：進階 DDL 輸入模式，瞭解使用 DDL 敘述句建立表格的步驟。
若要將資料載入 OCI 主控台所建立的表格，請按一下表格名稱。便會顯示表格的詳細資訊。按一下上傳資料。按一下選取要上傳的檔案，然後提供要上傳的 JSON 檔案。您可以從此處下載父項子項表格的 DDL 和 JSON 檔案。
使用左外部結合的範例
範例中使用的各種表格：
-
ticketticketNo LONG confNo STRING PRIMARY KEY(ticketNo) -
ticket.bagInfoid LONG tagNum LONG routing STRING lastActionCode STRING lastActionDesc STRING lastSeenStation STRING, lastSeenTimeGmt TIMESTAMP(4) bagArrivalDate TIMESTAMP(4) PRIMARY KEY(id) -
ticket.bagInfo.flightLegsflightNo STRING flightDate TIMESTAMP(4) fltRouteSrc STRING fltRouteDest STRING estimatedArrival TIMESTAMP(4), actions JSON PRIMARY KEY(flightNo) -
ticket.passengerInfocontactPhone STRING fullName STRING gender STRING PRIMARY KEY(contactPhone)
SQL 範例
範例 1：擷取已開票之所有乘客的詳細資料。
SELECT fullname, contactPhone,gender
FROM ticket a
LEFT OUTER JOIN ticket.passengerInfo b
ON a.ticketNo=b.ticketNo說明：這是目標表格 ticket 與其子項表格 passengerInfo 結合的結合範例。
輸出：
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
{"fullname":"Adelaide Willard","contactPhone":"421-272-8082","gender":"M"}
{"fullname":"Dierdre Amador","contactPhone":"165-742-5715","gender":"M"}
{"fullname":"Doris Martin","contactPhone":"289-564-3497","gender":"F"}
{"fullname":"Adam Phillips","contactPhone":"893-324-1064","gender":"M"}範例 1a：擷取車票號碼為 1762324912391 的乘客詳細資料。
SELECT fullname, contactPhone, gender
FROM ticket a
LEFT OUTER JOIN ticket.passengerInfo b
ON a.ticketNo=b.ticketNo
WHERE a.ticketNo=1762324912391說明：這是結合的範例，其中目標表格 ticket 與其子項表格 passengerInfo 結合，並套用篩選來限制結果。在此範例中，透過將篩選條件套用至結合的結果來限制結果集。您正在將結果限制為特定標牌號碼。
輸出：
{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}範例 2：擷取已開票之所有乘客的所有包裹明細。
SELECT * FROM ticket a
LEFT OUTER JOIN ticket.bagInfo b
ON a.ticketNo=b.ticketNo說明：這是目標表格 ticket 與其子項表格 bagInfo 結合的結合範例。
輸出：
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"tagNum":17657806255240,"routing":"MIA/LAX/MEL",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019-02-01T16:13:00.0000Z","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
"b":{"ticketNo":1762392135540,"id":79039899156435,"tagNum":17657806224224,"routing":"GRU/ORD/SEA",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"SEA",
"lastSeenTimeGmt":"2019-02-15T21:21:00.0000Z","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
"b":{"ticketNo":1762376407826,"id":7903989918469,"tagNum":17657806240229,"routing":"JFK/MAD",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MAD",
"lastSeenTimeGmt":"2019-03-07T13:51:00.0000Z","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"tagNum":17657806232501,"routing":"BZN/SEA/CDG/MXP",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MXP",
"lastSeenTimeGmt":"2019-03-22T10:17:00.0000Z","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}}範例 2a ：擷取特定票證號碼的所有行李明細。
SELECT * FROM ticket a
LEFT OUTER JOIN ticket.bagInfo b
ON a.ticketNo=b.ticketNo
WHERE a.ticketNo=1762324912391這是結合的範例，其中目標表格 ticket 與其子項表格 bagInfo 結合，並套用篩選來限制結果。在此範例中，透過將篩選條件套用至結合的結果來限制結果集。您正在將結果限制為特定標牌號碼。
輸出：
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}附註：
如果您將非結合述詞限制移至 ON 子句，結果集會包括符合 ON 子句條件的所有資料列。右側外部表格中不符合 ON 條件的資料列會填入 NULL 值，如下所示。SELECT * FROM ticket a
LEFT OUTER JOIN ticket.bagInfo b
ON a.ticketNo=b.ticketNo AND
a.ticketNo=1762324912391輸出：
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":null}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":null}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"}, "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN",
"lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},"b":null}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},"b":null}範例 3：擷取所有乘客的所有航段明細。
SELECT *FROM ticket a
LEFT OUTER JOIN ticket.bagInfo.flightLegs b
ON a.ticketNo=b.ticketNo;說明：這是目標表格 ticket 與其子系 ticketInfo 結合的結合範例。子代表格可以是表格下方階層的任何層次 (例如 fightLegs 是 bagInfo 的子項，即 ticket 的子項，因此 fightLegs 是 ticket 的子代)。
輸出：
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604","flightDate":"2019-02-01T06:00:00.0000Z",
"fltRouteSrc":"MIA","fltRouteDest":"LAX","estimatedArrival":"2019-02-01T11:00:00.0000Z",
"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667","flightDate":"2019-02-01T06:13:00.0000Z",
"fltRouteSrc":"LAX","fltRouteDest":"MEL","estimatedArrival":"2019-02-01T16:15:00.0000Z",
"actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM170","flightDate":"2019-03-15T08:13:00.0000Z",
"fltRouteSrc":"SLC","fltRouteDest":"BZN","estimatedArrival":"2019-03-15T10:14:00.0000Z",
"actions":[{"actionAt":"BZN","actionCode":"Offload to Carousel at BZN","actionTime":"2019-03-15T10:13:00Z"},
{"actionAt":"SLC","actionCode":"ONLOAD to BZN","actionTime":"2019-03-15T10:06:00Z"},
{"actionAt":"SLC","actionCode":"OFFLOAD from SLC","actionTime":"2019-03-15T09:59:00Z"}]}}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM490","flightDate":"2019-03-15T08:13:00.0000Z",
"fltRouteSrc":"CDG","fltRouteDest":"SLC","estimatedArrival":"2019-03-15T10:14:00.0000Z",
"actions":[{"actionAt":"CDG","actionCode":"ONLOAD to SLC","actionTime":"2019-03-15T09:42:00Z"},
{"actionAt":"CDG","actionCode":"BagTag Scan at CDG","actionTime":"2019-03-15T09:17:00Z"},
{"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-15T09:19:00Z"}]}}
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},
"b":{"ticketNo":1762324912391,"id":79039899168383,"flightNo":"BM936","flightDate":"2019-03-15T08:00:00.0000Z",
"fltRouteSrc":"MXP","fltRouteDest":"CDG","estimatedArrival":"2019-03-15T09:00:00.0000Z",
"actions":[{"actionAt":"MXP","actionCode":"ONLOAD to CDG","actionTime":"2019-03-15T08:13:00Z"},
{"actionAt":"MXP","actionCode":"BagTag Scan at MXP","actionTime":"2019-03-15T07:48:00Z"},
{"actionAt":"MXP","actionCode":"Checkin at MXP","actionTime":"2019-03-15T07:38:00Z"}]}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},
"b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM79","flightDate":"2019-02-15T01:00:00.0000Z",
"fltRouteSrc":"GRU","fltRouteDest":"ORD","estimatedArrival":"2019-02-15T11:00:00.0000Z",
"actions":[{"actionAt":"GRU","actionCode":"ONLOAD to ORD","actionTime":"2019-02-15T01:21:00Z"},
{"actionAt":"GRU","actionCode":"BagTag Scan at GRU","actionTime":"2019-02-15T00:55:00Z"},
{"actionAt":"GRU","actionCode":"Checkin at GRU","actionTime":"2019-02-14T23:49:00Z"}]}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"}
,"b":{"ticketNo":1762392135540,"id":79039899156435,"flightNo":"BM907","flightDate":"2019-02-15T01:21:00.0000Z",
"fltRouteSrc":"ORD","fltRouteDest":"SEA","estimatedArrival":"2019-02-15T21:22:00.0000Z",
"actions":[{"actionAt":"SEA","actionCode":"Offload to Carousel at SEA","actionTime":"2019-02-15T21:16:00Z"},
{"actionAt":"ORD","actionCode":"ONLOAD to SEA","actionTime":"2019-02-15T20:52:00Z"},
{"actionAt":"ORD","actionCode":"OFFLOAD from ORD","actionTime":"2019-02-15T20:44:00Z"}]}}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},
"b":{"ticketNo":1762376407826,"id":7903989918469,"flightNo":"BM495","flightDate":"2019-03-07T07:00:00.0000Z",
"fltRouteSrc":"JFK","fltRouteDest":"MAD","estimatedArrival":"2019-03-07T14:00:00.0000Z",
"actions":[{"actionAt":"MAD","actionCode":"Offload to Carousel at MAD","actionTime":"2019-03-07T13:54:00Z"},
{"actionAt":"JFK","actionCode":"ONLOAD to MAD","actionTime":"2019-03-07T07:00:00Z"},
{"actionAt":"JFK","actionCode":"BagTag Scan at JFK","actionTime":"2019-03-07T06:53:00Z"},
{"actionAt":"JFK","actionCode":"Checkin at JFK","actionTime":"2019-03-07T05:03:00Z"}]}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM386","flightDate":"2019-03-22T07:23:00.0000Z",
"fltRouteSrc":"CDG","fltRouteDest":"MXP","estimatedArrival":"2019-03-22T10:24:00.0000Z",
"actions":[{"actionAt":"MXP","actionCode":"Offload to Carousel at MXP","actionTime":"2019-03-22T10:15:00Z"},
{"actionAt":"CDG","actionCode":"ONLOAD to MXP","actionTime":"2019-03-22T10:09:00Z"},
{"actionAt":"CDG","actionCode":"OFFLOAD from CDG","actionTime":"2019-03-22T10:01:00Z"}]}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM578","flightDate":"2019-03-22T07:23:00.0000Z",
"fltRouteSrc":"SEA","fltRouteDest":"CDG","estimatedArrival":"2019-03-21T23:24:00.0000Z",
"actions":[{"actionAt":"SEA","actionCode":"ONLOAD to CDG","actionTime":"2019-03-22T11:26:00Z"},
{"actionAt":"SEA","actionCode":"BagTag Scan at SEA","actionTime":"2019-03-22T10:57:00Z"},
{"actionAt":"SEA","actionCode":"OFFLOAD from SEA","actionTime":"2019-03-22T11:07:00Z"}]}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},
"b":{"ticketNo":1762355527825,"id":79039899197492,"flightNo":"BM704","flightDate":"2019-03-22T07:00:00.0000Z",
"fltRouteSrc":"BZN","fltRouteDest":"SEA","estimatedArrival":"2019-03-22T09:00:00.0000Z",
"actions":[{"actionAt":"BZN","actionCode":"ONLOAD to SEA","actionTime":"2019-03-22T07:23:00Z"},
{"actionAt":"BZN","actionCode":"BagTag Scan at BZN","actionTime":"2019-03-22T06:58:00Z"},
{"actionAt":"BZN","actionCode":"Checkin at BZN","actionTime":"2019-03-22T05:20:00Z"}]}}範例 3a：擷取特定機票號碼的所有航段明細。
SELECT * FROM ticket a
LEFT OUTER JOIN ticket.bagInfo.flightLegs b
ON a.ticketNo=b.ticketNo
WHERE a.ticketNo=1762344493810這是結合的範例，其中目標表格 ticket 與其子系 bagInfo 結合，並套用篩選來限制結果。在此範例中，透過將篩選條件套用至結合的結果來限制結果集。您正在將結果限制為特定標牌號碼。
結果有兩列，表示此機票號碼有兩個航段。
輸出：
"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604",
"flightDate":"2019-02-01T06:00:00.0000Z","fltRouteSrc":"MIA","fltRouteDest":"LAX",
"estimatedArrival":"2019-02-01T11:00:00.0000Z",
"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},
"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667",
"flightDate":"2019-02-01T06:13:00.0000Z","fltRouteSrc":"LAX","fltRouteDest":"MEL",
"estimatedArrival":"2019-02-01T16:15:00.0000Z",
"actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}範例 4：為所有乘客的所有行李提取行李 ID 與躍點數目。
SELECT b.id,count(*) AS NUMBER_HOPS
FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b
ON a.ticketNo=b.ticketNo GROUP BY b.id說明：您可以根據行李 ID (使用 GROUP BY) 來分組資料，並取得每個行李的飛行航段數目 (使用 count())。
輸出：
{"id":79039899168383,"NUMBER_HOPS":3}
{"id":79039899156435,"NUMBER_HOPS":2}
{"id":7903989918469,"NUMBER_HOPS":1}
{"id":79039899165297,"NUMBER_HOPS":2}
{"id":79039899197492,"NUMBER_HOPS":3}範例 4a：尋找指定乘客之所有行李的躍點數目。
SELECT b.id,count(*) AS NUMBER_HOPS
FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b
ON a.ticketNo=b.ticketNo
WHERE a.ticketNo=1762355527825 GROUP BY b.id說明：您可以根據行李 ID (使用 GROUP BY) 來分組資料，並取得每個行李的班機航段數目 (使用 count())。此外，您可以篩選特定標牌號碼的結果。
輸出：
{"id":79039899197492,"NUMBER_HOPS":3}範例 5：提取 2019 年之後到達之所有行李的行李識別碼與遞送明細。
SELECT b.id, routing
FROM ticket a LEFT OUTER JOIN ticket.bagInfo b
ON a.ticketNo=b.ticketNo
WHERE CAST (b.bagArrivalDate AS Timestamp(0))
>= CAST ("2019-01-01T00:00:00" AS Timestamp(0))說明：這是目標表格 ticket 與其子項表格 bagInfo 結合的結合範例。篩選條件會套用至 bagArrivalDate。CAST 函數可用來將字串轉換為「時戳」，然後比較值。
輸出：
{"id":79039899197492,"routing":"BZN/SEA/CDG/MXP"}
{"id":79039899165297,"routing":"MIA/LAX/MEL"}
{"id":79039899168383,"routing":"MXP/CDG/SLC/BZN"}
{"id":79039899156435,"routing":"GRU/ORD/SEA"}
{"id":7903989918469,"routing":"JFK/MAD"}查詢 API 範例
若要執行查詢，請使用 NoSQLHandle.query() API。
Download the full code TableJoins.java from the examples here.
/* fetch rows based on joins*/
private static void fetchRows(NoSQLHandle handle,String sql_stmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sql_stmt);
QueryIterableResult results = handle.queryIterable(queryRequest)) {
System.out.println("Query results:");
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
/* fetching rows using left outer joins*/
String sql_stmt_loj ="SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo";
System.out.println("Fetching data using Left outer joins:");
fetchRows(handle,sql_stmt_loj);若要執行查詢，請使用 borneo.NoSQLHandle.query() 方法。
Download the full code TableJoins.py from the examples here.
# Fetch data from the table based on joins
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))
sql_stmt_loj='SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo'
print('Fetching data using Left Outer Joins ')
fetch_data(handle,sql_stmt_loj)若要執行查詢，請使用 Client.Query 函數。
Download the full code TableJoins.go from the examples here.
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()))
}
}
querystmt_loj:= "SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo"
fmt.Println("Fetching data using Left Outer Joins")
fetchData(client, err,querystmt_loj)若要執行查詢，請使用 query 方法。
JavaScript: Download the full code TableJoins.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);
}
}
const stmt_loj = 'SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo';
console.log("Fetching data using Left Outer Joins");
await fetchData(handle,stmt_loj);TypeScript: Download the full code TableJoins.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 stmt_loj = 'SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo';
console.log("Fetching data using Left Outer Joins");
await fetchData(handle,stmt_loj);若要執行查詢，您可以呼叫 QueryAsync 方法或呼叫 GetQueryAsyncEnumerable 方法，然後重複產生的非同步可列舉。
Download the full code TableJoins.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.Row
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}
private const string stmt_loj ="SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo.flightLegs b ON a.ticketNo=b.ticketNo";
Console.WriteLine("Fetching data using Left Outer Joins: ");
await fetchData(client,stmt_loj);