使用 AND 和 OR 的 Oracle SQL 动态查询性能更高

Question

有没有更好的查询来解决这个问题？

1 汽车有多种类型。

我想要这样的东西：（ps这是不正确的，我知道）

select * from car join type on car.id = type.id_car 
where type = 'SUV' 
and type = '4x4' 
and (type = 'blue' or type = 'red') 
and type = 'US' 
and (type = 'Manual' or type = 'Automatic' or 'SemiAutomatic') 
and type = 'diesel' and so on.

我的解决方案是：

select * from car 
where numberOfANDType = (select count(1) from Type where car.id = type.id_car and type in ('suv', '4x4', 'us', 'diesel')) 
and exists (select 1 from type where car.id = type.id_car and type in ('blue', 'red'))
and exists (select 1 from type where car.id = type.id_car and type in ('manual', 'automatic' or 'SemiAutomatic');

等等。

ps：我知道AND使用的条件个数
ps2：这些条件是动态的。

不管怎样：我为每个类型都有一个组列，并且对于 OR 组中使用的类型，我在此列中具有相同的值。 SUV 的 GROUP = 1，蓝色的 GROUP = 2，红色的 GROUP = 2 等等。 因此，我对 TYPE 列和组的计数进行查询，以查看是否覆盖了所有组。

Select id from car join type on .. where type in ('SUV', 'blue', 'red') group by id having count(distinct group) > 2;

谢谢。

ps3：感谢所有对这个问题投反对票的人，你们非常友善。

Answer 1

这样就可以做到：

select car.* 
from car INNER JOIN (
                     select id, count(*) countMatches
                     from car INNER JOIN type on car.id = type.id_car
                     where type in ('suv', '4x4', 'us', 'diesel','blue', 
                                    'red','manual', 'automatic')
                     group by id
                     having count(*) = 6
                    ) matches ON car.id = matches.id

Answer 2

扩展我对 Cosmin 答案的评论，这应该有效：

select car.id 
from car 
join type on car.id = type.id_car 
where type.type in 
('SUV','4x4','blue','red','US','Manual','Automatic','SemiAutomatic','diesel')
group by car.id
having count(distinct type.group)=6

Answer 3

我认为解决这个问题的更好方法是具体说明类型并为这些类型创建列：

select * from car 
where type = 'SUV' 
and drive_type = '4x4' 
and (colour = 'blue' or colour = 'red') 
and origin = 'US' 
and (transmission = 'Manual' or transmission = 'Automatic') 
and fuel_type = 'diesel'

然后您可以使用索引来提高查询的性能。

Answer 4

select * from car c1 where c1.id in
(
  select c.id from car c
  inner join Type t on c.id = t.id_car and c.type in ('suv', '4x4', 'us', 'diesel', 'blue', 'red', 'manual', 'automatic')
  group by c.id
  having count(distinct *) > 5
)

Answer 5

你的设计很糟糕。

每个组值都应该进入一个新列。

所以类型表应该是：

id_car color transmission fuel origin_country etc
1      blue  automatic    gas  UK
....

查询是：

select * 
from car join type on (car.id = type.id_car)
where color in ('red', 'blue')
and transmision in ('automatic')
and country in ('US')
etc.