这个 MySQL 查询（带有子查询）可以优化吗？

发布于 2024-08-23 10:55:24 字数 329 浏览 3 评论 0原文

有没有更好的方法让这个查询工作？我正在寻找一种更有效的解决方案（如果有的话）。

SELECT `unitid`, `name` FROM apartmentunits WHERE aptid IN (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 4 AND condnum = 1
) AND aptid IN (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 2 AND condnum = 1
) ORDER BY name ASC

原文

Is there any better way to make this query work? I'm looking for a more efficient solution, if there is one available.

SELECT `unitid`, `name` FROM apartmentunits WHERE aptid IN (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 4 AND condnum = 1
) AND aptid IN (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 2 AND condnum = 1
) ORDER BY name ASC

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╰沐子 2024-08-30 10:55:24

我想你想在这里自我加入。将表 rentconditionsmap 连接到其自身，并指示连接两侧的条件。然后将该查询的结果加入到 apartmentunits 中。

（注意：尚未测试过，可能需要一些调整......）

SELECT `unitid`, `name` FROM `apartmentunits` 
 WHERE `unitid` IN (
        SELECT `unitid` FROM `rentconditionsmap` r1, `rentconditionsmap` r2
         WHERE r1.`unitid` = r2.`unitid`
           AND r1.`rentcondid` = 4
           AND r1.`condnum` = 1
           AND r2.`rentcondid` = 2
           AND r2.`condnum` = 1)
 ORDER BY `name` ASC

I think you want a self-join here. Join table rentconditionsmap to itself, and indicate the conditions on either side of the join. Then join the results of that query into apartmentunits.

(Note: haven't tested this, may require some tweaking...)

SELECT `unitid`, `name` FROM `apartmentunits` 
 WHERE `unitid` IN (
        SELECT `unitid` FROM `rentconditionsmap` r1, `rentconditionsmap` r2
         WHERE r1.`unitid` = r2.`unitid`
           AND r1.`rentcondid` = 4
           AND r1.`condnum` = 1
           AND r2.`rentcondid` = 2
           AND r2.`condnum` = 1)
 ORDER BY `name` ASC

回复收藏 0 原文

迷你仙 2024-08-30 10:55:24

SELECT a.`unitid`, 
       a.`name` 
FROM   apartmentunits a 
       INNER JOIN rentconditionsmap r 
       ON a.aptid = r.aptid
   AND
       r.rentcondid in (2,4)
   AND 
       r.condnum = 1
ORDER BY a.`name`

SELECT a.`unitid`, 
       a.`name` 
FROM   apartmentunits a 
       INNER JOIN rentconditionsmap r 
       ON a.aptid = r.aptid
   AND
       r.rentcondid in (2,4)
   AND 
       r.condnum = 1
ORDER BY a.`name`

回复收藏 0 原文

蒲公英的约定 2024-08-30 10:55:24

是的，使用联接。大多数 DBMS 都会优化连接，这样它就不需要拉出它不需要的行。 MySQL 仍然使用嵌套循环，但我相信 Oracle 会使用散列连接。

所以这个查询可能更好地表达为

Select `unitid`, `name` FROM apartmentunits au
INNER JOIN rentconditionsmap rcm1
USING (aptid)
INNER JOIN rentconditionsmap rcm2
USING (aptid)
WHERE rcm1.rentcondid = 4
AND rcm1.condnum = 1
AND rcm2.rendcondid = 2
AND rcm2.condnum = 1

Yes, using joins. Most DBMSes will optimise the join such that it need not pull rows it doesn't have to. MySQL still uses nested loops, but I belive Oracle will use hash joins.

So this query might better be expressed as

Select `unitid`, `name` FROM apartmentunits au
INNER JOIN rentconditionsmap rcm1
USING (aptid)
INNER JOIN rentconditionsmap rcm2
USING (aptid)
WHERE rcm1.rentcondid = 4
AND rcm1.condnum = 1
AND rcm2.rendcondid = 2
AND rcm2.condnum = 1

回复收藏 0 原文

~没有更多了~