Mysql：在两列之间选择值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12824871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Selecting values between two columns
提问by Nicolas Boulet-Lavoie
I'm trying to select a value between 2 columns. Here is my dataset
我正在尝试在 2 列之间选择一个值。这是我的数据集
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（介于 from 和 to 之间）的行。所以这是我正在使用的查询：
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.
我试过使用 < 和 > 等。但是，我总是得到两个结果。任何帮助将非常感激。
采纳答案by Vikdor
SO, you don't want the lower bound to be inclusive, right?
所以，您不希望下限包含在内，对吗？
SET @value = 2;
SELECT * FROM table WHERE from > @value AND @value <= to;
回答by Juliano Lima
You could try this:
你可以试试这个：
SELECT * FROM `table` WHERE 2 BETWEEN `from` AND `to`
回答by RedFilter
Query 1:
查询 1：
select *
from `table`
where `from` < 2 and `to` >= 2
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.
注意：使用这种方法，您将不会为 value 返回任何行0，除非您修改查询以适应它。
回答by user1859658
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
