用于选择给定整数列表中任何一个位于columnA和columnB之间的记录的SQL查询

Question

在columnA和columnB整数值定义的范围内，如何从表中获取任何整数列表中的记录？

当比较列值而不是由一对列定义的范围时，我知道IN运算符。

例如：select * from mytable where mytable.colA in (1,3,5,6);将获得colA为1、3、5或6的所有记录。

在靶场有类似的东西吗？或者我应该喜欢：

select * from mytable where 1 between mytable.colA and mytable.colb OR 3 between mytable.colA and mytable.colb OR 5 between mytable.colA and mytable.colb OR 6 between mytable.colA and mytable.colb;

Answer 1

也许是这样的：

select distinct mytable.*
from mytable
join (select 1 nr union all select 3 union all select 5 union all select 6) n
    on n.nr between mytable.colA and mytable.colb

更新：

刚刚在MariaDB (10.0.19)和1M行索引表上进行了测试。您的原始查询速度更快。

Answer 2

一个常见的策略是设置一个临时表，并使用它在主表上加入。

一种简单的设置方法如下：

DECLARE @TempList table (LookFor int not null)

INSERT @TempList (LookFor) values
  (1)
 ,(3)
 ,(5)
 ,(6)

因为这是一个表，所以可以使用查询逻辑来填充它。

接下来，将它加入到目标表中。对于上面的例子：

SELECT mt.*
 from myTable mt
  inner join @TempList tl
   on tl.LookFor = mt.ColA

而且，如果我的解释是正确的，这可能是你真正想要的：

SELECT mt.*
 from myTable mt
  inner join @TempList tl
   on tl.LookFor between mt.ColA and mt.ColB