Mysql:在两列之间选择值

I'm trying to select a value between 2 columns. Here is my dataset

我在两列之间选择一个值。这是我的数据集

id    from    to    price
1     0.00    2.00  2.50
2     2.00    3.00  3.00
3     3.00    4.00  4.50

My goal, if I have a value of 2 is to select the line with the ID 1 (between from and to). So here is the query I'm using :

我的目标是，如果值为2，那么选择ID为1的行(从和到之间)。这是我使用的查询:

select * from table where 2 between from and to;

And here are the results that MySQL returns when executing this query :

下面是MySQL执行此查询时返回的结果:

id    from    to    price
1     0.00    2.00  2.50
2     2.00    3.00  3.00

And the result I'm looking for is the following :

我要找的结果是:

id    from    to    price
1     0.00    2.00  2.50

I've tried using < and >, etc. But, I'm always getting two results. Any help would be much appreciated.

我试过用 <和> ，等等，但是，我总是得到两个结果。如有任何帮助，我们将不胜感激。

4 个解决方案

#1

SO, you don't want the lower bound to be inclusive, right?

你不希望下界是包容的，对吧?

SET @value = 2;
SELECT * FROM table WHERE from > @value AND @value <= to;

#2

You could try this:

你可以试试这个:

SELECT * FROM `table` WHERE 2 BETWEEN `from` AND `to`

#3

Query 1:

查询1:

select * 
from `table` 
where `from` < 2 and `to` >= 2

SQL Fiddle Example

SQL小提琴的例子

Output:

输出:

| ID | FROM | TO | PRICE |
--------------------------
|  1 |    0 |  2 |     3 |

Query 2:

查询2:

select * 
from `table` 
where `from` < 2.001 and `to` >= 2.001

Output:

输出:

| ID | FROM | TO | PRICE |
--------------------------
|  2 |    2 |  3 |     3 |

Note: With this approach, you will get no rows back for value 0, unless you modify the query to accommodate that.

注意:使用这种方法，您将不会返回值为0的行，除非您修改查询以适应它。

#4

You can also try this ,

你也可以试试这个，

select * from table where (from-to) = 2  // if you want the exact distance to be 2 
select * from table where (from-to) >= 2 // Distance more than 2