我有如下查询，我想优化它们。
car_wheels AS (
SELECT wheel FROM devices WHERE type = '4x4'
),
white_wheels AS (
SELECT wheel FROM devices WHERE color = 'white'
),
car_white_wheels AS (
SELECT * FROM car_wheels INTERSECT SELECT * FROM white_wheels
)
SELECT wheel FROM devices JOIN users ON devices.user_id = user.id WHERE wheel IN (SELECT wheel FROM car_white_wheels) AND users.organization_id = 999;
我有一个在这里使用内部连接的想法。这样对吗？我还能在这里轻松优化什么？
我想为给定的 user.organization_id 找到白色的 car_wheels。
只做这个怎么样？
select wheel
from devices d join
users u
on d.user_id = u.id
where (type = '4x4' and color = 'white') and
u.organization_id = 999;
我错过了什么吗？
或者，如果wheel可以在多行上具有特征：
select wheel
from devices d join
users u
on d.user_id = u.id
where (type = '4x4' or color = 'white') and
u.organization_id = 999
group by wheel
having sum(case when type = '4x4' then 1 else 0 end) > 0 and
sum(case when color = 'white' then 1 else 0 end) > 0';
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句