11
2019
04

oracle中join右表最多只取一条指定的数据

一般情况下,如果类型这种:

       select * from A
       left join B 
       ON A.con_id=B.con_id;

假设A中有1条数据,B中与A关联的数据有2条或以上,查出的结果是有2条或以上,有些情况我们需要以A表为准,B表只查想要的一条记录


下面我们来做个对比(这里语句复杂一点,B表别名为VCT)

1、正常语句:

SELECT GAT.POLICY_ID,
       GAT.CAR_ID,
       GAT.DLR_CUST_NO,
       GAT.DLR_ID,
       GAT.TRAVEL_TAX, /*车船税*/
       GAT.INSUER_GATHER_ID,
       GAT.UPDATE_CONTROL_ID,
       der.UPDATE_CONTROL_ID as DER_UPDATE_CONTROL_ID,
       INSUER_GATHER_CODE AS 结算单号,
       GAT.INSURANT, /*客户名称*/
       GAT.INSURANT_PHONE, /*客户电话*/
       GAT.VIN, /*VIN码*/
       CAR.CAR_LICENSE_NO, /*车牌号*/
       E.CAR_SERIES_CN, /*车系*/
       NVL(CAR.SMALL_CAR_TYPE_CODE, CT.SMALL_CAR_TYPE_CODE) CAR_TYPE_CODE, /*车型*/
       VCT.CAR_TYPE_CN,/*车型名称*/
       CAR.ENGINE_NO, /*发动机号*/
       DECODE(GAT.IS_REGISTRATION, '1', '已登记', '未登记') AS 登记状态,
       TO_CHAR(GAT.Created_Date, 'YYYY-MM-DD') 下单日期 /*下单日期*/,
       TO_CHAR(S.INVOICE_DATE, 'YYYY-MM-DD') SALE_DATE /*销售日期*/,
       TO_CHAR(S.DELIVERY_DATE, 'YYYY-MM-DD') DELIVERY_DATE, /*交车日期*/
       DLR.DLR_SHORT_NAME /*专营店名称*/
  FROM T_AI_BU_INSUER_GATHER GAT
  LEFT JOIN T_MDM_DLR_CAR CAR
    ON CAR.CAR_ID = GAT.CAR_ID
   AND CAR.IS_ENABLE = '1'
  LEFT JOIN v_ai_car_series C
    ON CAR.CAR_SERIES_CODE = C.CAR_SERIES_CODE
   AND C.CAR_BRAND_CODE = CAR.CAR_BRAND_CODE
  LEFT JOIN T_MDM_ORG_DLR DLR
    ON DLR.DLR_ID = GAT.DLR_ID
   AND DLR.IS_ENABLE = '1'
  LEFT JOIN t_mdm_dlr_car_service S
    ON S.CAR_ID = CAR.CAR_ID
   and s.dlr_id = gat.dlr_id
/*AND S.SALE_TYPE IN ('1','2','4','6','5','7')*/
  LEFT JOIN T_MDM_VE_CAR_CONFIG B
    ON CAR.CAR_CONFIG_ID = B.CAR_CONFIG_ID
  LEFT JOIN T_MDM_SMALL_CAR_TYPE CT
    ON B.SMALL_CAR_TYPE_ID = CT.SMALL_CAR_TYPE_ID
  LEFT JOIN T_MDM_VE_CAR_SERIES E
    ON E.CAR_SERIES_CODE = CAR.CAR_SERIES_CODE
   AND E.CAR_BRAND_CODE = CAR.CAR_BRAND_CODE
  left join T_AI_BU_INSURE_ORDER der
    on gat.policy_id = der.policy_id
  left join 
  V_TEMP_MY_BASE_ALLCARTYPE  VCT 
  
   on CAR.CAR_BRAND_CODE=VCT.CAR_BRAND_CODE
   and NVL(CAR.SMALL_CAR_TYPE_CODE, CT.SMALL_CAR_TYPE_CODE) = VCT.CAR_TYPE_CODE
 where 1 = 1
   AND ((GAT.BALANCE_STATUS = '1') OR
       (GAT.IS_RECEIVABLE = '0' AND GAT.RETURN_AMOUNT = 0) OR
       (GAT.IS_RECEIVABLE != '1'))
   AND GAT.IS_ENABLE = '1'

查询出来的结果:

2、先处理B表,只查一条与关联字段对应数据:

SELECT GAT.POLICY_ID,
       GAT.CAR_ID,
       GAT.DLR_CUST_NO,
       GAT.DLR_ID,
       GAT.TRAVEL_TAX, /*车船税*/
       GAT.INSUER_GATHER_ID,
       GAT.UPDATE_CONTROL_ID,
       der.UPDATE_CONTROL_ID as DER_UPDATE_CONTROL_ID,
       INSUER_GATHER_CODE AS 结算单号,
       GAT.INSURANT, /*客户名称*/
       GAT.INSURANT_PHONE, /*客户电话*/
       GAT.VIN, /*VIN码*/
       CAR.CAR_LICENSE_NO, /*车牌号*/
       E.CAR_SERIES_CN, /*车系*/
       NVL(CAR.SMALL_CAR_TYPE_CODE, CT.SMALL_CAR_TYPE_CODE) CAR_TYPE_CODE, /*车型*/
       VCT.CAR_TYPE_CN,/*车型名称*/
       CAR.ENGINE_NO, /*发动机号*/
       DECODE(GAT.IS_REGISTRATION, '1', '已登记', '未登记') AS 登记状态,
       TO_CHAR(GAT.Created_Date, 'YYYY-MM-DD') 下单日期 /*下单日期*/,
       TO_CHAR(S.INVOICE_DATE, 'YYYY-MM-DD') SALE_DATE /*销售日期*/,
       TO_CHAR(S.DELIVERY_DATE, 'YYYY-MM-DD') DELIVERY_DATE, /*交车日期*/
       DLR.DLR_SHORT_NAME /*专营店名称*/
  FROM T_AI_BU_INSUER_GATHER GAT
  LEFT JOIN T_MDM_DLR_CAR CAR
    ON CAR.CAR_ID = GAT.CAR_ID
   AND CAR.IS_ENABLE = '1'
  LEFT JOIN v_ai_car_series C
    ON CAR.CAR_SERIES_CODE = C.CAR_SERIES_CODE
   AND C.CAR_BRAND_CODE = CAR.CAR_BRAND_CODE
  LEFT JOIN T_MDM_ORG_DLR DLR
    ON DLR.DLR_ID = GAT.DLR_ID
   AND DLR.IS_ENABLE = '1'
  LEFT JOIN t_mdm_dlr_car_service S
    ON S.CAR_ID = CAR.CAR_ID
   and s.dlr_id = gat.dlr_id
/*AND S.SALE_TYPE IN ('1','2','4','6','5','7')*/
  LEFT JOIN T_MDM_VE_CAR_CONFIG B
    ON CAR.CAR_CONFIG_ID = B.CAR_CONFIG_ID
  LEFT JOIN T_MDM_SMALL_CAR_TYPE CT
    ON B.SMALL_CAR_TYPE_ID = CT.SMALL_CAR_TYPE_ID
  LEFT JOIN T_MDM_VE_CAR_SERIES E
    ON E.CAR_SERIES_CODE = CAR.CAR_SERIES_CODE
   AND E.CAR_BRAND_CODE = CAR.CAR_BRAND_CODE
  left join T_AI_BU_INSURE_ORDER der
    on gat.policy_id = der.policy_id
  left join 
  (select * from (
          select CAR_BRAND_CODE,CAR_TYPE_CN,CAR_TYPE_CODE,(row_number() over (partition by CAR_TYPE_CODE order by  IS_ENABLE desc,IS_OEM_BRAND desc)) as rowIndex from V_TEMP_MY_BASE_ALLCARTYPE) TEMP_VCT where rowIndex = 1 )  VCT 
  
   on CAR.CAR_BRAND_CODE=VCT.CAR_BRAND_CODE
   and NVL(CAR.SMALL_CAR_TYPE_CODE, CT.SMALL_CAR_TYPE_CODE) = VCT.CAR_TYPE_CODE
 where 1 = 1
   AND ((GAT.BALANCE_STATUS = '1') OR
       (GAT.IS_RECEIVABLE = '0' AND GAT.RETURN_AMOUNT = 0) OR
       (GAT.IS_RECEIVABLE != '1'))
   AND GAT.IS_ENABLE = '1'

查询出来的结果:


3、关键语句分析

select *
  from (select CAR_BRAND_CODE,
               CAR_TYPE_CN,
               CAR_TYPE_CODE,
               (row_number()
                over(partition by CAR_TYPE_CODE order by IS_ENABLE desc,IS_OEM_BRAND desc)) as rowIndex
                /*先按关联字段CAR_TYPE_CODE分组重新取行号,并且按IS_ENABLE,IS_OEM_BRAND倒序(根据自己的需求)排列*/
          from V_TEMP_MY_BASE_ALLCARTYPE) TEMP_VCT
 where rowIndex = 1/*取分组中行号为1的数据,相当于已经按关联字段排重了*/


这种情况的需求比较常用,可以参考这样查询,思路也是比较清晰,就是先把要关联的右表按关联字段按自己的需求去重。

或者可以像下面这样用Group By,但是我还不知道怎么实现灵活控制,比如一条IS_ENABLE字段,怎么保证优先拿IS_ENABLE=1的,如果没有再拿IS_ENABLE=0的?

select CAR_TYPE_CODE,max(CAR_TYPE_CN) from V_TEMP_MY_BASE_ALLCARTYPE group by CAR_TYPE_CODE;

还有一种就是用子查询,子查询是能灵活控制,但大部分情况下子查询效率比join要低一些。

有兴趣的可以一起交流。



版权声明:
作者:真爱无限 出处:http://www.pukuimin.top 本文为博主原创文章版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接.
« 上一篇下一篇 »

相关文章:

评论列表:

1.分享互联网  2019-8-23 9:16:20 回复该留言
不知道你了解sql server吗?有个更好的办法outer apply.

oracle也有这个字段。

http://www.itpub.net/thread-1837981-1-1.html

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。