用于多个值范围的sql中的Between运算符

Question

我在一列上有5个不同的范围数据，我要像一样进行筛选

10-20 
110-120 
250-260

我正在使用以下内容，但它抛出了不正确的结果。

Select * from test where
testnumber between '10' and '20'
or testnumber between '110' and '120'
or testnumber between '250' and '260'

有人能建议我如何在SQL中实现这一点或正确的方法吗。感谢

Answer 1

试试这个：

select * from test where testnumber between ('10') and ('20')

Answer 2

如果可以将范围放入具有BeginNum和EndNum列的表中，则可以执行

SELECT * 
FROM test t
INNER JOIN Ranges r on t.testnumber BETWEEN r.BeginNum and r.EndNum

Answer 3

即使您不能创建一个表，您也可能有一些其他选项：

Select *
from (values(1),(15),(50),(115),(200),(255),(300)) test(testnumber)
where testnumber between '10' and '20'
or testnumber between '110' and '120'
or testnumber between '250' and '260'

或

declare @test (testnumber int);
insert @test values(1),(15),(50),(115),(200),(255),(300);
Select *
from @test
where testnumber between '10' and '20'
or testnumber between '110' and '120'
or testnumber between '250' and '260'