java - 使用左连接在 postgres 数据库中进行 SQL 查询

Question

好吧，我做了一些研究，显然，根据从右侧连接的表，左连接可以返回超过 1 条记录。

我的查询是:

SELECT 
 ord.ID AS ord_id,
 oli.sfid AS oli_sfid,
 ord.HasMSISDN__c AS ord_HasMSISDN__c,
 ord.dealer_code__c AS ord_dealer_code__c, 
 ord.recordtypeid AS ord_recordtypeid,
 ord.order_number__c AS ord_order_number__c,
 ord.status AS ord_status,
 ord.opportunityid AS ord_opportunityid,
 ord.sfid AS ord_sfid,
 ord.cancelled_by__c AS ord_cancelled_by__c,
 ord.cancelled_on__c AS ord_cancelled_on__c,
 ord.created_by__c AS ord_created_by__c,
 ord.created_on__c AS ord_created_on__c,
 ord.docusign_email_address__c AS ord_docusign_email_address__c,
 ord.esignature_resent_to__c AS ord_esignature_resent_to__c, 
 ord.esignature_resent_by__c AS ord_esignature_resent_by__c,
 ord.esignature_resent_on__c AS ord_esignature_resent_on__c, 
 ord.pricebook2id AS ord_pricebook2id,
 cont.opportunity__c AS cont_opportunity__c,
 cont.sfid AS cont_sfid,
 opp.isclosed AS opp_isclosed,
 opp.sfid AS opp_sfid,
 opp.recordtypeid AS opp_recordtypeid,
 opp.pricebook2id AS opp_pricebook2id, 
 accban.sfid AS accban_sfid, 
 accban.ban__c AS accban_ban__c,
 usr.sfid AS usr_sfid  
 FROM fullsbxsalesforce.order ord
 LEFT JOIN fullsbxsalesforce.contract cont ON ord.contractid = cont.sfid
 LEFT JOIN fullsbxsalesforce.opportunity opp ON cont.opportunity__c = opp.sfid
 LEFT JOIN fullsbxsalesforce.user usr ON (ord.dealer_code__c = usr.dealer_code_bd__c OR ord.dealer_code__c = usr.Dealer_Code_Co_Sell__c OR ord.dealer_code__c = usr.Rep_Dealer_Code__c OR ord.dealer_code__c = usr.dealer_code_secondary__c) LEFT JOIN fullsbxsalesforce.account_ban_tax_id__c accban ON ord.ban_number__c = accban.ban__c 
 LEFT JOIN fullsbxsalesforce.orderitem oli ON ord.sfid = oli.orderid 
 WHERE ord.sfid = 'SPECIFIC ID'

最初，我的印象是这会返回 1 行。我错了，它返回 3 行，因为订单上附加了 3 个不同的 OLI。我如何确保或更改我的逻辑，以便我以相同的顺序返回一组 OLI，或者只返回第一个 OLI，这样我就不会处理 3 个重复项

Answer 1

如果您希望以相同的顺序返回行，只需添加 ORDER BY <col_name_list>查询末尾的子句。

OLI 是唯一值吗？哪个表定义了 OLI？

如果您始终希望此查询始终返回一行，只需添加 LIMIT 1到查询的末尾。

如果您的查询返回多个 OLI，而您只希望每个 OLI 有一行，那么您可以使用窗口函数:

SELECT ...
FROM (
  -- Your initial query with new field added
  SELECT ...
  ROW_NUMBER() OVER(PARTITION BY OLI_field_name ORDER BY <ordering_clause>) AS RowRank
  FROM ...
) src
WHERE RowRank = 1

这将根据 <OLI_field_name> 返回一行.

更新
如果您只想让每个 OLI 有一行并保留所有详细信息，请使用窗口函数方法。像这样:

SELECT *
FROM (
  SELECT 
   ord.ID AS ord_id,
   oli.sfid AS oli_sfid,
   ord.HasMSISDN__c AS ord_HasMSISDN__c,
   ord.dealer_code__c AS ord_dealer_code__c, 
   ord.recordtypeid AS ord_recordtypeid,
   ord.order_number__c AS ord_order_number__c,
   ord.status AS ord_status,
   ord.opportunityid AS ord_opportunityid,
   ord.sfid AS ord_sfid,
   ord.cancelled_by__c AS ord_cancelled_by__c,
   ord.cancelled_on__c AS ord_cancelled_on__c,
   ord.created_by__c AS ord_created_by__c,
   ord.created_on__c AS ord_created_on__c,
   ord.docusign_email_address__c AS ord_docusign_email_address__c,
   ord.esignature_resent_to__c AS ord_esignature_resent_to__c, 
   ord.esignature_resent_by__c AS ord_esignature_resent_by__c,
   ord.esignature_resent_on__c AS ord_esignature_resent_on__c, 
   ord.pricebook2id AS ord_pricebook2id,
   cont.opportunity__c AS cont_opportunity__c,
   cont.sfid AS cont_sfid,
   opp.isclosed AS opp_isclosed,
   opp.sfid AS opp_sfid,
   opp.recordtypeid AS opp_recordtypeid,
   opp.pricebook2id AS opp_pricebook2id, 
   accban.sfid AS accban_sfid, 
   accban.ban__c AS accban_ban__c,
   usr.sfid AS usr_sfid,
   ROW_NUMBER() OVER(PARTITION BY oli.sfid ORDER BY <order_col>) AS RowRank -- Assigns a rank to each row with the same oli.sfid value
  FROM fullsbxsalesforce.order ord
  LEFT JOIN fullsbxsalesforce.contract cont ON ord.contractid = cont.sfid
  LEFT JOIN fullsbxsalesforce.opportunity opp ON cont.opportunity__c = opp.sfid
  LEFT JOIN fullsbxsalesforce.user usr ON (ord.dealer_code__c = usr.dealer_code_bd__c OR ord.dealer_code__c = usr.Dealer_Code_Co_Sell__c OR ord.dealer_code__c = usr.Rep_Dealer_Code__c OR ord.dealer_code__c = usr.dealer_code_secondary__c)
  LEFT JOIN fullsbxsalesforce.account_ban_tax_id__c accban ON ord.ban_number__c = accban.ban__c 
  LEFT JOIN fullsbxsalesforce.orderitem oli ON ord.sfid = oli.orderid 
  WHERE ord.sfid = 'SPECIFIC ID'
) src
WHERE RowRank = 1 -- Only get one row per oli.sfid value

这假设oli.sfid是 OLI ID

只需更改外层SELECT *返回除 RowRank 以外的所有字段.另外，修改 <order_col>值以确定要为每个 oli.sfid 返回哪一行.