Oracle SQL -查询子表以查找匹配的父表

Question

Table vehicle_master
vehicle_id | vin
1          | 1234
2          | 5678
3          | 9123

Table vehicle_inventory

vehicle_id | key       | value
-----------|-----------|------
1          | Make      | Honda       
1          | Model     | Civic        
1          | Year      | 2004        
2          | Make      | Toyota
2          | Model     | Corolla
2          | Year      | 2005        
2          | color     | white   (every row is optnl: key/row may or may not exist in db)
3          | Make      | Honda       
3          | Model     | Civic        
3          | Year      | 2004     
3          | color     | blue

我正在试图找到一辆汽车，与给定的制造，模型和颜色。可以在输入查询中指定颜色限制，但它可能在DB中不可用。如果DB中的汽车没有指定颜色，即使在查询中指定了颜色，也应该匹配它。

到目前为止，我使用的SQL：

select * from vehicle_master vm where 1=1
and exists(
    select 1 from vehicle_inventory vi
    where vi.vehicle_id=vm.vehicle_id
          and (vi.key=':make' and vi.value=':makeValue')
)
and exists(
    select 1 from vehicle_inventory vi
    where vi.vehicle_id=vm.vehicle_id
          and (vi.key=':model' and vi.value=':modelValue')
)
and exists(
    select 1 from vehicle_inventory vi
    where vi.vehicle_id=vm.vehicle_id
          and (vi.key=':color' and vi.value=':colorValue')
)

输入:丰田，模型:花冠，颜色:白色，我得到了预期的结果2倍5678。

如果我输入Make: Honda，Model:Civic，Color:red，我没有得到匹配或结果，因为本田的颜色键不存在于DB中。我希望在这个实例中忽略颜色限制，因为没有db条目。我希望只有当颜色有一个键和值时，才在搜索中考虑颜色。

如果带键的行存在，如何使键/值对有条件地匹配，如果DB中不存在键/值行，则忽略限制？

编辑:每个键都是可选的。

Answer 1

我会在这里使用聚合。例如，为了寻找白色本田思域，我们可以尝试：

SELECT
    vm.vehicle_id,
    vm.vin
FROM vehicle_inventory vi
INNER JOIN vehicle_master vm
    ON vi.vehicle_id = vm.vehicle_id
GROUP BY vm.vehicle_id, vm.vin
HAVING
    SUM(CASE WHEN vi.key = 'Make'  AND vi.value = 'Honda' THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN vi.key = 'Model' AND vi.value = 'Civic' THEN 1 ELSE 0 END) > 0 AND
    (SUM(CASE WHEN vi.key = 'color' AND vi.value = 'white' THEN 1 ELSE 0 END) > 0 OR
     SUM(CASE WHEN vi.key = 'color' THEN 1 ELSE 0 END) = 0);

HAVING子句的最后两个术语是，在简单的英语中，查找颜色为白色或根本没有颜色的车辆。

Answer 2

尝试使用子查询和聚合，如：

select b.vehicle_id, b.vin
from
    (select * from vehicle_master vm where
    exists(
        select 1 from vehicle_inventory vi
        where vi.vehicle_id=vm.vehicle_id
              and (vi.key='Make' and vi.value=':makeValue')
    )
    union all
    select * from vehicle_master vm where
    exists(
        select 1 from vehicle_inventory vi
        where vi.vehicle_id=vm.vehicle_id
              and (vi.key='Model' and vi.value=':modelValue')
    )
    union all
    select * from vehicle_master vm where
    exists(
        select 1 from vehicle_inventory vi
        where vi.vehicle_id=vm.vehicle_id
              and (vi.key='Color' and vi.value=':colorValue')
    )) b
group by b.vehicle_id, b.vin 
having count(*)=3;

您只需要3个变量，键就是常量。

Answer 3

通过不更改查询的结构，可以将第三个子查询更改为不存在的约束。您可以检查它在vehicle_inventory中是否存在一个颜色与您要寻找的记录颜色不同的记录：

and NOT exists(
    select 1 from vehicle_inventory vi
    where vi.vehicle_id=vm.vehicle_id
    and (vi.key=':color' and vi.value != ':colorValue')
)