java - 使用左联接的Postgres数据库中的SQL查询

好的，我做了一些研究，很明显，一个左连接可以根据从右连接的表返回超过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'

最初，我的印象是这将返回一行。我搞错了，它返回3行，因为订单上附加了3个不同的奥利。我怎样才能保证，或者改变我的逻辑，使我返回的奥利的集合顺序相同，或者只返回第一个奥利，这样我就不会处理3个重复的奥利

最佳答案

如果希望以相同的顺序返回行，只需在查询的最后添加一个ORDER BY <col_name_list>子句。
奥利是一种独特的价值吗？哪个表定义奥利？
如果始终希望此查询返回一行，只需在查询的末尾添加一个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只有一行并保留所有详细信息，请使用window函数方法。像这样的：

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返回哪一行。