我有一个房间和设备的数据库.我想查询数据库并返回一个房间列表,例如电视,收音机,坐着和冰箱(eq1,eq2,eq3,….,eqN).
我有以下SELECT语句：
select * from rooms r where
exists (select id from equipments where eq_id='eq1' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq2' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq3' and room_id=r.id)
.......
and
exists (select id from equipments where eq_id='eqN' and room_id=r.id)
有没有什么方法可以优化或缩短它？
最佳答案
缩短你可以
select *
from rooms r
where @N = (select count(distinct eq_id)
from equipments
where eq_id IN ('eq1','eq2',...,'eqN') and room_id=r.id)
编辑
但不确定它是否真的会让它更快……恰恰相反,带有EXISTS和EXISTS的版本有机会在第一个false上修剪执行分支,上面必须实际计算不同的值(遍历所有记录)和看看那个价值是什么.
所以你应该考虑更快的事情：
>一次通过与房间相关的所有记录(一个相关的子查询)或
>为每个房间运行N(最坏情况)相关(但高度选择性的子查询)
这取决于你的数据的统计数据(我认为如果大多数房间里没有所有需要的设备,那么你的初始版本应该更快,如果大多数房间都有所有设备,那么建议的版本可能表现更好;此外,如果EXISTS版本更快,请首先尝试最有可能失败的查询,即首先检查最稀有的设备)
您也可以尝试使用GROUP BY的版本
select r.*
from rooms r join
equipments e on r.id = e.room_id
group by r.id
where eg_id in ('eq1','eqN')
having count(distinct e.eq_id) = @N
(以上sql未经测试)