如何在范围列表之间选择行

Question

我使用JPA和JPQL查询从MySQL数据库检索数据。但我的问题是合乎逻辑的，而不是具体的语言(我认为).

表SELECT WHERE 的所有条目，其中一列在 BETWEEN 中，是范围的列表。

例如：

SELECT x FROM myTable 
WHERE x BETWEEN 2 AND 5 
   OR x BETWEEN 7 AND 9 

而范围(2-5，7-9)来自另一个表：

 RANGES         ex:   id | from | to
––––––––––            –––––––––––––––
 id:   INT              1|     2|   5
 from: INT              2|     7|   9
 to:   INT              3|     9|  15
                        4|    17| null

My problem a)

是，Ranges列表是动态的，可以是一个范围，如

x BETWEEN 2 AND 5

或多个范围，如

x BETWEEN 2 AND 5 OR x BETWEEN 7 AND 9 OR x BETWEEN ....

我的问题b)

，Ranges也可以打开，就像

x >= 17

My problem c)

是，还有其他的WHERE条件，限制了结果。其中一些WHERE条件比BETWEEN条件更“限制”。所以他们是第一位的，以使整个查询更快。比方说

... WHERE x%2=0 AND x BETWEEN 2 AND 5

对于c)，我考虑了子查询，例如：

SELECT x FROM myTable 
WHERE x IN (
        SELECT x FROM myTable 
        WHERE x BETWEEN 2 AND 5 
          OR  x BETWEEN 7 AND 9
     )
 AND y .. something something

“好”之处在于，我使用Java和JPA来创建查询，这样我就可以动态地创建查询。但是在滥用字符串连接来动态创建SQL查询之前，我希望在纯SQL中找到一个更干净的解决方案(或者在JPQL中更好)。

Answer 1

首先，and是错误的逻辑。"x“不能同时介于2、5、7和9之间，这是数学上不可能的。所以，我猜你打算or。

然后，您可以使用exists完成此操作。

select t1.*
from table1 t1
where exists (select 1
              from table2 t2
              where t1.x between t2.fromval and t2.toval
             );

Answer 2

回答问题(b)：

如果有开放边界条件，则应更改@Gordon中的条件：

 where (t1.x between t2.fromval and t2.toval)
       OR ((t1.x >= t2.fromval)  AND (t2.toval IS NULL))
       OR ((t1.x <= t2.toval)  AND (t2.fromval IS NULL))
       OR ((t2.toval IS NULL) AND (t2.fromval IS NULL)) --super conditions always TRUE :)

此外，您还可以使用以下条件：

where t1.x between COALESCE(t2.fromval,t1.x) and COALESCE(t2.toval,t1.x)