一般情况下,如果类型这种:
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要低一些。
有兴趣的可以一起交流。