将子句转换为连接 [英] Converting between clause to joins

问题描述

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

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

我有一个名为autumn4.ip的表，我有三列：id，start，end（all ints）。

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

如何进行连接，以便使用但不是由BETWEEN，而是加入。

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 some one join the above two queries to get one row of data that would be equivalent to

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

只是不要问我为什么这样做:)
但我知道你woudl这样：

Just dont ask my why I am doing it :) but I know you woudl so:

我试图完成的是使用2个索引，开始和结束。如果我使用BETWEEN和/或开始，结束的复合索引。只使用开始索引，而不是结束。现在请不要告诉我我错了。我只是想这样如果我为开始和结束创建单个索引，我已经对这个

I am trying to accomplish is to use 2 indexes, start and end. if i use BETWEEN and/or a composite index of start,end. Only start index is used and not the end. now please dont tell me that I am wrong. I just want it that way. I have done alot of research on this

做了大量研究，只有一个被使用。给我一个查询，其中两个都使用

if i do create single indexes for both start and end, only one is used. give me a query in which both are used

推荐答案

你希望请求只选择开始和结束之间的第一行，使用JOIN？

You want that the request selects only the 1st row between start and end, using JOINs ?

编辑：

忘记我的回答！没有需要2桌我的想法！这是一个索引问题比其他任何事情.....

forget my answer! No need for 2 tables for what I thought! It's more an index problem than anything else.....

你可以尝试在START上放一个索引，另一个在END（看起来你有两个列的索引，不是吗？），并按如下所示选择：

You can try to put an index on START, and a different one on END (it seems that you have 1 index with both columns, don't you?), and do your select like this:

SELECT * FROM autumn4.ip WHERE :number >= START AND :number <= END ORDER BY START LIMIT 1; 

(or ORDER BY START DESC if you want the biggest start) .

。

这篇关于将子句转换为连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！