在 ORACLE IN 子句中使用元组和元组中一个元素的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17689563/
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
Using tuples in ORACLE IN clause and a condition for one element in the tuple
提问by Bharath ABK
I have seen many questions here for using tuples in the IN clause. My situation is a little different from the others. General usage of tuples in IN clause will look as below
我在这里看到了很多关于在 IN 子句中使用元组的问题。我的情况和其他人有点不同。IN 子句中元组的一般用法如下所示
Select * from MY_TABLE
where (id,name,date) IN ((1,'new','10-JUL-13'),(2, 'old','09-JUN-13'))
Considering the above query, my requirement is to retrieve the records with id and name values along with date in a particular range. lets say
考虑到上述查询，我的要求是检索具有 id 和 name 值以及特定范围内的日期的记录。让我们说
effectiveDate <= date <= termDate
I'm using ORACLEdatabase and MyBatisORM. I'll get data as a list of objects, so when I use mybatis I can use a foreach/forloop to populate the tuples, but when I want to use condition for one of those values from the object.
我正在使用ORACLE数据库和MyBatisORM。我将获取数据作为对象列表，因此当我使用 mybatis 时，我可以使用foreach/for循环来填充元组，但是当我想对来自对象的这些值之一使用条件时。
When I use Mybatis for one value read from a list, the where clause as below
当我使用 Mybatis 从列表中读取一个值时， where 子句如下
<where>
and (id,name) IN
<foreach item="object" collection="data" open="(" separator=","close=")">
(#{object.id},#{object.name})
</foreach>
</where>
I have to include the condition in the loop as well.
我还必须在循环中包含条件。
Waiting for the expert advice. Thanks in advance.
等待高手指点。提前致谢。
回答by Gordon Linoff
Are you looking for something like this?
你在寻找这样的东西吗？
select *
from MY_TABLE
where (id, name) in ((1,'new'), (2, 'old')) and
date between effectiveDate and termDate
This looks for the pairs in a list and then checks for the dates between a range of dates.
这会在列表中查找对，然后检查日期范围之间的日期。
EDIT:
编辑：
I think you want to break this into multiple clauses, one for each set of values:
我想你想把它分成多个子句，每组值一个：
where (id = 1 and name = 'new' and date between eff1 and term1) or
(id = 2 and name = 'old' and date between eff2 and term2) or
. . .
回答by Bharath ABK
The above solution for my question is rewritten below for Mybatis users
上面针对我的问题的解决方案针对Mybatis用户重写如下
<where>
<foreach item="object" collection="data" separator="OR">
(id = #{object.id} AND name = #{object.name} AND (effDt < #{object.date} < termDt))
</foreach>
</where>
There won't be a big performance difference when you use either IN or OR to separate the data/conditions in the WHERE clause.
当您使用 IN 或 OR 来分隔 WHERE 子句中的数据/条件时，不会有很大的性能差异。
FYI : *http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause*
