具有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：这些条件是动态的。

Anywayz：我为每个类型都有一个GROUP列，对于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

扩展我对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 2

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

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 3

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 4

你的设计不好。

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

所以类型表应该是：

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.

Answer 5

这样做：

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