查询多个EXIST

Question

我有一个房间和设备的数据库。我想查询数据库并返回带有电视、收音机、卫星和冰箱等房间的列表（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)

有什么方法可以优化或缩短它吗？

Answer 1

要缩短您可以

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 AND 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','eq2',...,'eqN')
having count(distinct e.eq_id) = @N

（以上 SQL 未测试）

Answer 2

试试这个（我没有任何数据库可用于测试它，还要考虑性能）

select * from
    rooms r,
    (
        select count(distinct id) as cnt, id from equipments  where eq_id in ('eq1','eq2') group by id
    ) as sub
where sub.id = r.id 
and sub.cnt >= 2 'Options count

注意：2 - 这是您需要的选项数量。例如，它们是：“eq1”、“eq2”

Answer 3

select * from rooms r where 
(select count(id) from equipments where eq_id='eq1' and room_id=r.id) > 0
and
...

Answer 4

使用存储过程。

以下是 mysql 的过程：

DELIMITER $

CREATE DEFINER=`root`@`%` PROCEDURE `GetRooms`(IN roomtable TEXT, IN equipmenttable TEXT, IN equipments TEXT )
BEGIN
    DECLARE statement text;
    DECLARE Pos int;
    DECLARE cond text;
    DECLARE element text;   
    DECLARE tmpTxt text;   

    set tmpTxt = equipments;
    set cond = "";
    set Pos = instr(tmpTxt,';');
    while Pos <> 0 do
    set element = substring(tmpTxt, 1, Pos-1);

    if cond <> "" then
        set cond = concat(cond,' and ');
    end if;

    set cond = concat(cond,'exists (select id from ' , equipmenttable ,' where eq_id=''' ,  element ,''' and room_id=r.id) ');


    set tmpTxt = replace(tmpTxt, concat(element,';'), '');


    set Pos = instr(tmpTxt,';');
    end while; 

    if tmpTxt <> "" then
        if cond <> "" then
            set cond = concat(cond,' and ');
        end if;

        set cond = concat(cond,'exists (select id from ' , equipmenttable ,' where eq_id=''' ,  tmpTxt ,''' and room_id=r.id) ');
    end if;

    SET @statement = concat('Select * FROM ' ,  roomtable , " WHERE " , cond , ";");

    PREPARE stmt FROM @statement;
    EXECUTE stmt;
END

执行方法： CALL GetRooms('RoomTableName','EquipmentTableName','EquipmentIDs')

示例：

Call GetRooms('rooms','equipemnts','eq1;eq2;eq3');

希望这会有所帮助。

Answer 5

要更快地执行查询，请使用 Exists

select * 
from rooms as r 
where exists (
select * 
from equipments 
where eq_id IN ('eq1','eq2',..,'eqN') and r.id= equipments.room_id);