SQL 选择 BETWEEN 列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3746840/
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
Select BETWEEN column values
提问by Gon?alo Queirós
I'm trying to use the BETWEEN with column names instead of direct values, something like this:
我正在尝试将 BETWEEN 与列名而不是直接值一起使用，如下所示：
SELECT * FROM table WHERE column1 BETWEEN column2 AND column3;
This is returning something like 17 rows, but if i write:
这将返回类似 17 行的内容，但如果我写：
SELECT * FROM table WHERE (column1 <= column2 AND column1 >= column3) OR (column1 >= column2 AND column1 <= column3)
i get around 600 rows.. In both cases i only get rows where column1 value is actually the middle value, but 2nd method gives me much more results, so 1st method has something wrong with it.
我得到大约 600 行。在这两种情况下，我只得到 column1 值实际上是中间值的行，但是第二种方法给了我更多的结果，所以第一种方法有问题。
I suspect the problem might be on using BETWEEN clause with column names, instead of pure values, and somehow SQL is converting the column names to actual values..its strange, but can someone enlighten me please? Thanks
我怀疑问题可能在于使用带有列名的 BETWEEN 子句而不是纯值，并且 SQL 以某种方式将列名转换为实际值......很奇怪，但有人可以启发我吗？谢谢
回答by codaddict
SELECT * FROM table WHERE column1 BETWEEN column2 AND column3; # gives 17 rows
is same as
与
SELECT * FROM table WHERE (column1 >= column2 AND column1 <= column3) # gives 17 rows
Because of your addition check of
由于您的附加检查
(column1 <= column2 AND column1 >= column3)
which is ORed, you get additional rows.
这是ORed，你会得到额外的行。
回答by Charles Bretana
Between A And Bassumes that A<B, i.e., that the first expression in the Between, (A), is less than the second expression, (B) it does not check or execute with the opposite option.
Between A And B假设A<B，即，Between 中的第一个表达式 ( A) 小于第二个表达式 ( B) 它不检查或使用相反的选项执行。
e.g., if you put Where 3 Between 4 And 2no rowswill be returned:
例如，如果你放Where 3 Between 4 And 2没有行将被返回：
or, if you write
或者，如果你写
Select Case When 3 Between 4 and 2 then 'true' else 'false' end
Select Case When 3 Between 4 and 2 then 'true' else 'false' end
it will return false
它会回来 false
回答by Paul Michaels
Your logic for the two statements is not the same:
您对这两个语句的逻辑不一样：
SELECT * FROM table WHERE (column1 <= column2 AND column1 >= column3) OR (column1 >= column2 AND column1 <= column3)
Has two clauses. Remove the first and you should have the same results as your between statement.
有两个子句。删除第一个，您应该获得与 between 语句相同的结果。
SELECT * FROM table WHERE (column1 >= column2 AND column1 <= column3)
