如何使用索引进行两列之间的查询过滤 [英] How to use indexes for a query filtering BETWEEN two columns

问题描述

我需要一个可以在mysql，postgres和其他主要DB上运行的联接的通用SQL查询.

I need a generic sql query for joins that can run on mysql, postgres and other major DBs.

我有一个名为autumn4.ip的表，该表具有三列:id, start, end(所有整数).

I have a table named autumn4.ip with three columns: id, start, end (all ints).

我如何进行联接，以便不是通过BETWEEN而是通过JOIN来使用BUT?

How do I make a join so that I make use of BUT not by BETWEEN but by JOINs?

类似(伪):

SELECT * FROM autumn.ip WHERE :number-constant >= start
ORDER BY start DESC LIMIT 1;

SELECT * FROM autumn.ip WHERE :number-constant <= end
ORDER BY start ASC LIMIT 1;

如何将以上两个查询结合起来获得等同的结果:

How to join the above two queries to get the equivalent of:

SELECT * FROM autumn4.ip WHERE :number-constant BETWEEN START AND END LIMIT 1;

我正在尝试在start和end上使用2个索引.如果我在(start, end)上使用BETWEEN和/或复合索引，则仅使用开始索引，而不使用结束索引.现在请不要告诉我我错了.我只是想要那样.我对此做了很多研究.

I am trying to use 2 indexes on start and end. If I use BETWEEN and/or a composite index on (start, end), only start index is used and not the end. now please don't tell me that I am wrong. I just want it that way. I have done a lot of research on this.

如果我确实为开始和结束创建单个索引，则仅使用一个.我正在寻找同时使用两者的查询.

If I do create single indexes for both start and end, only one is used. I am looking for a query that uses both.

推荐答案

简单

SELECT * FROM autumn.ip WHERE :number-constant >= start
ORDER BY start DESC LIMIT 1
UNION
SELECT * FROM autumn.ip WHERE :number-constant <= end
ORDER BY start ASC LIMIT 1;

应该工作.

但是，让我质疑您的研究质量...

HOWEVER, let me question your research quality...

这篇关于如何使用索引进行两列之间的查询过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！