使用左外部結合搭配父項 - 子項表格

JOIN 是用來結合兩個或多個表格的資料列 (根據它們之間的相關資料欄)。在階層式表格中，子項表格會繼承其父項表格的主索引鍵資料欄。這會以隱含方式執行，而不會在子項的 CREATE TABLE 敘述句中包含父項資料欄。階層中的所有表格都有相同的分區索引鍵資料欄。

「左外部結合 (LOJ)」是可讓您指定結合子句的其中一個結合作業。

• 左外部結合簡介

• 使用左外部結合的範例

左外部結合簡介

「左外部結合 (LOJ)」是可讓您指定結合子句的其中一個結合作業。它會保留第一個 (左) 表格中不相符的資料列，並將它們與第二個 (右) 表格中的 NULL 資料列結合。這表示右側表格中沒有相符資料列的所有左側資料列都會顯示在結果中，並與 NULL 值配對以取代右側資料列。

在 LOJ 中，結果集中的欄位順序一律由上而下。這表示結果集中的輸出順序一律來自祖系表，然後是子系表。無論結合的順序為何，此為真。

左外部結合的特性：

• 在相同階層中查詢多個表格

• 它是 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 檔案。

使用左外部結合的範例

範例中使用的各種表格：

• ticket - 許可

  ticketNo LONG
  confNo STRING
  PRIMARY KEY(ticketNo)

• 中文 (繁體)

  id LONG
  tagNum LONG
  routing STRING
  lastActionCode STRING
  lastActionDesc STRING
  lastSeenStation STRING,
  lastSeenTimeGmt TIMESTAMP(4)
  bagArrivalDate TIMESTAMP(4)
  PRIMARY KEY(id)

• 機票。包資訊。航班航段

  flightNo STRING
  flightDate TIMESTAMP(4)
  fltRouteSrc STRING
  fltRouteDest STRING
  estimatedArrival TIMESTAMP(4),
  actions JSON
  PRIMARY KEY(flightNo)

• 機票。乘客資訊

  contactPhone STRING
  fullName STRING
  gender STRING
  PRIMARY KEY(contactPhone)

• SQL 範例

• 查詢 API 範例

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

說明：您可以根據包裝袋識別碼 (使用 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

說明：您可以根據包裝袋識別碼 (使用 GROUP BY) 來群組資料，並取得每個包裝袋的航段數目 (使用 count())。此外，您還可以篩選特定標牌號碼的結果。

輸出：

{"id":79039899197492,"NUMBER_HOPS":3}

範例 5：提取 2019 年之後抵達之所有包裝袋的包裝袋 ID 與製程明細。

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 範例

• Java
• Python
• 移至
• Node.js
• C#

若要執行查詢，請使用 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() 方法。

從此處的範例下載完整代碼 TableJoins.py 。

# 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 函數。

從此處的範例下載完整代碼 TableJoins.go 。

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：請從此處的範例下載完整程式碼 TableJoins.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);
   }
}

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：從此處的範例下載完整的程式碼 TableJoins.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 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);

相關主題

• 建立表格
• 新增資料