很难给予一个具体的答案，真正看到所有的数据或模型，但我解释你的要求：

• “如果找不到客户与CAR_COL的联接，则返回使用CAR_CAT”*

利用您现有的代码，我们可以添加一个列来“区分”数据的优先级，例如

select *
from (
    select 
      a.customer_id ,
      a.car_code ,
      b.description as code_desc ,
      a.price,
      row_number() over ( partition by customer_id, car_code
         order by case when domain = 'car_col' then 1 else 2 end ) as rating
    from product a
    left join (select * from reference_codes 
               where domain in ('car_col', 'car_cat') ) b
    on a. car_code   =  b. code
)
where rating = 1

如果示例数据如下所示：

WITH
    reference_codes (DOMAIN, CAR_CODE, DESCRIPTION) AS
        (
            Select  'car_col',  12, 'blue'      From  Dual Union All
            Select  'car_col',  23, 'black'     From  Dual Union All
            Select  'car_col',  45, 'red'       From  Dual Union All
            Select  'car_col',  67, 'green'     From  Dual Union All
                    
            Select  'car_cat',  24, 'yellow'    From  Dual Union All
            Select  'car_cat',  45, 'red'       From  Dual Union All
            Select  'car_cat',  70, 'purple'    From  Dual Union All
            Select  'car_cat',  90, 'row'       From  Dual 
        ),
    product (CUSTOMER_ID, CODE, PRICE) AS
        (
            Select 123, 12, 100     From  Dual Union All
            Select 123, 23, 100     From  Dual Union All
            Select 345, 45, 120     From  Dual Union All
            Select 345, 45, 120     From  Dual Union All
            Select 678, 67, 110     From  Dual Union All
            Select 678, 24, 110     From  Dual Union All
            Select 908, 45, 130     From  Dual Union All
            Select 908, 70, 130     From  Dual 
        )

...那么您的查询结果将如上所示。虽然选择了PRICE列，但我们无法看到您的问题中的值。

CUSTOMER_ID       CODE CODE_DESC      PRICE
----------- ---------- --------- ----------
        123         12 blue             100 
        123         23 black            100 
        345         45 red              120 -- one row less if 'car_col' is missing
        345         45 red              120 
        345         45 red              120 
        345         45 red              120 
        678         24 yellow           110 
        678         67 green            110 
        908         70 purple           130 
        908         45 red              130 
        908         45 red              130

要得到你预期的结果（如果没有不同的价格），你可以只添加DISTINCT关键字，以获得你想要的：

Select DISTINCT
... ... ... your query
CUSTOMER_ID       CODE CODE_DESC      PRICE
----------- ---------- --------- ----------
        123         12 blue             100 
        123         23 black            100 
        345         45 red              120 
        678         24 yellow           110 
        678         67 green            110 
        908         45 red              130 
        908         70 purple           130

......如果价格不同，您可以使用按以下方式分组的聚合

Select    
    a.CUSTOMER_ID ,
    a.CODE ,
    b.DESCRIPTION "CODE_DESC",
    AVG(a.PRICE) "AVG_PRICE"
From
    product a
Left Join 
    ( Select * From reference_codes Where DOMAIN IN('car_col', 'car_cat') ) b
    ON(a.CODE =  b.CAR_CODE)    
Group By
    a.CUSTOMER_ID ,
    a.CODE ,
    b.DESCRIPTION
Order By 
    a.CUSTOMER_ID
CUSTOMER_ID       CODE CODE_DESC  AVG_PRICE
----------- ---------- --------- ----------
        123         12 blue             100 
        123         23 black            100 
        345         45 red              120 
        678         24 yellow           110 
        678         67 green            110 
        908         45 red              130 
        908         70 purple           130

即使缺少'car_col'，您的查询也可以通过DISTINCT或聚合进行调整（取决于PRICE）：

WITH
    reference_codes (DOMAIN, CAR_CODE, DESCRIPTION) AS
        (
            Select  'car_col',  12, 'blue'      From  Dual Union All
            Select  'car_col',  23, 'black'     From  Dual Union All
--Select    'car_col',  45, 'red'       From  Dual Union All
            Select  'car_col',  67, 'green'     From  Dual Union All
                    
            Select  'car_cat',  24, 'yellow'    From  Dual Union All
            Select  'car_cat',  45, 'red'       From  Dual Union All
            Select  'car_cat',  70, 'purple'    From  Dual Union All
            Select  'car_cat',  90, 'row'       From  Dual