在ORACLE IN子句中使用元组以及元组中一个元素的条件 [英] Using tuples in ORACLE IN clause and a condition for one element in the tuple

问题描述

我在这里看到很多关于在IN子句中使用元组的问题.我的情况与其他情况略有不同.IN子句中元组的一般用法如下所示:

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

    Select * from MY_TABLE
    where (id,name,date) IN ((1,'new','10-JUL-13'),(2, 'old','09-JUN-13'))

考虑到以上查询，我的要求是检索具有ID和名称值以及特定范围内的日期的记录.可以说

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

    effectiveDate <= date <= termDate  

我正在使用 ORACLE 数据库和 MyBatis ORM.我将以对象列表的形式获取数据，所以当我使用mybatis时，可以使用 foreach/for 循环来填充元组，但是当我想对来自对象.

I'm using ORACLE database and MyBatis ORM. I'll get data as a list of objects, so when I use mybatis I can use a foreach/for loop to populate the tuples, but when I want to use condition for one of those values from the object.

当我将Mybatis用于从列表中读取的一个值时，where子句如下

When I use Mybatis for one value read from a list, the where clause as below

    <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.

推荐答案

您是否正在寻找类似的东西?

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.

我认为您想将其分解为多个子句，每组值一个:

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
      . . .

这篇关于在ORACLE IN子句中使用元组以及元组中一个元素的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！