我需要对可以在mysql,postgres和其他主要数据库上运行的连接进行通用SQL查询。
我有一个名为 autumn4.ip
的表,有三列:id, start, end
(所有整数)。
如何进行连接,以便我不是通过 BETWEEN 而是通过 JOIN 来使用?
喜欢(伪):
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;
如何联接上述两个查询以获得等效的:
SELECT * FROM autumn4.ip WHERE :number-constant BETWEEN START AND END LIMIT 1;
我正在尝试在 start
和 end
上使用 2 个索引。如果我在(start, end)
上使用 BETWEEN 和/或复合索引,则只使用开始索引而不是结束索引。现在请不要告诉我我错了。我只是想要这样。我对此做了很多研究。
如果我确实为开始和结束创建了单个索引,则只使用一个索引。我正在寻找同时使用两者的查询。
一个简单的
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;
应该工作。
但是,让我质疑您的研究质量...
窍是按顺序在复合索引中添加第二列DESCENDING
。
使用基本的 b 树索引,这将在 PostgreSQL 中使用(如果满足一些一般前提条件):
CREATE INDEX foo ON autumn.ip (col_start, col_end DESC)
一般原则应适用于任何具有 b 树索引的 RDBMS。
此外,对于像您这样的范围查询,GiST 索引在 PostgreSQL 中可能更有效。考虑这个密切相关的答案:
PostgreSQL 索引不用于查询范围
旁白:切勿使用start
或end
等保留字作为标识符,特别是当您试图与多个RDBMS保持兼容时。