sql - 有没有如何优化与 2 内连接到同一个表的查询？

Question

我有这个问题:我有一个包含这 3 个表的模型:Linha、Itinerario 和 Rua在 Itinerario 中，我有 Linha 表的引用 id 和 Rua 的 id 引用。在我的代码中，我收到了两个关于 idRua 的参数，并且我必须返回所有 Linhas，其中我有 Itinerario 对 Rua 的引用和两个 idRua...在这个例子中，我有 idRua = 1 和 idRua = 2。

SELECT l.codigo, l.linha, l.idEmpresa, l.idLinha 
FROM Linha l 
INNER JOIN Itinerario i1 ON i1.idLinha = l.idLinha 
INNER JOIN Itinerario i2 ON i2.idLinha = l.idLinha 
WHERE i1.ida = i2.ida and i1.idRua = 1 and i2.idRua = 2 
ORDER BY l.linha

问题是我在该表 Itinerario 中获得了 2 个内部联接，并且查询变慢了...有没有办法优化它？是否有一些带有“AND”条件的“IN”运算符或类似的东西？我正在使用 SQLite。

Answer 1

可能有一种方法可以消除自连接。

如果我没看错，您希望 Itinerario 中的 idLinha 包含同一个 ida 的 idRua = 1 和 idRua = 2。我注意到这只是一个过滤条件，因为选择中的所有内容都来自 linha。

以下得到这个条件:

SELECT idlinha
From itinerario
GROUP BY idlinha, ida
having max(case when idRua = 1 then 1 else 0 end) = 1 and
       max(case when idRua = 2 then 1 else 0 end) = 1

现在，我们可以在“in”或“join”子句中使用它，如:

SELECT l.codigo, l.linha, l.idEmpresa, l.idLinha 
FROM Linha l
where l.idlinha in (SELECT idlinha
                    From itinerario
                    GROUP BY idlinha
                    having max(case when idRua = 1 then 1 else 0 end) = 1 and
                           max(case when idRua = 2 then 1 else 0 end) = 1
                   )
order by l.linha

group by 可能会比自加入更快。