首页 文章

WHERE子句中SQL查询中的IF条件

提问于
浏览 次
-2

我有像这样的Oracle sql查询

SELECT *
FROM test
WHERE 1 =1
AND req_no IN
  (SELECT req_no
  FROM test
  WHERE date1 IS NOT NULL
  AND date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  )

问题是 WHERE 条件没有't work properly. In above condition if ' date1 ' is NOT NULL then I want to check ' date1 ' in between ' FROM ' and ' TO'date . EDIT 我添加了样本表数据

Test table
From date: 1-JUN-2012
To date: 30-JUN-2012

record      date1          date2            date3
1       12-JUN-2012     13-JUN-2012         12-JUN-2012
2       null            null                12-JUN-2012
3       null            3-JUN-2012          null
4       12-JUN-2012     null                null
5       12-JUN-2012     13-JUL-2012         12-JUN-2012
6       null                null             null

在上面的记录中，它将返回记录号= 1,2,3,4而不是第6记录 .

sql oracle where-clause

4 回答

  • 0

    您可以像这样使用 OR ：

    WHERE date1 IS NULL
  OR date1 BETWEEN '1-JUN-12' AND '30-JUN-12'

    您的整个查询应该是：

    SELECT *
FROM test
WHERE 1 =1
AND req_no IN
  (SELECT req_no
  FROM test
  WHERE date1 IS NULL
  OR date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  )
    回复于
  • 0

    1=1 是多余的 . 既然你说要检查date1是否在日期范围之间，或者如果它是null，那么也没问题，试试：

    SELECT *
FROM test
WHERE req_no IN
  (SELECT req_no
  FROM test
  WHERE date1 BETWEEN to_date('06/01/2012','mm/dd/yyyy') 
         AND to_date('06/30/2012','mm/dd/yyyy') 
        OR date1 is null
  )
    回复于
  • 0

    我认为你不需要子查询你可以使用单个查询直接选择你的相关记录，你也不需要检查 date1 是否为null

    SELECT *  FROM test
  WHERE TO_DATE('12/2/2003','MM/DD/YYYY') <= date1 
AND TO_DATE('12/5/2003','MM/DD/YYYY') >= date1
    回复于
  • 0

    找到了一些解决方法，但它非常冗长

    SELECT *
FROM dates
WHERE 1  =1
AND( 
  no IN
  ( SELECT no FROM dates WHERE date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date2 is null and date3 is null ) 
  or 
   no IN
  ( SELECT no FROM dates WHERE date2 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date1 is null and date3 is null ) 
  or 
   no IN
  ( SELECT no FROM dates WHERE date3 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date1 is null and date2 is null ) 
   or 
   no IN
  ( SELECT no FROM dates WHERE date3 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date1 BETWEEN '1-JUN-12' AND '30-JUN-12' and date2 BETWEEN '1-JUN-12' AND '30-JUN-12' )
   or 
   no IN
  ( SELECT no FROM dates WHERE date1 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date1 BETWEEN '1-JUN-12' AND '30-JUN-12' and date3 is null )
    or 
   no IN
  ( SELECT no FROM dates WHERE date3 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date3 BETWEEN '1-JUN-12' AND '30-JUN-12' and date2 is null )
   or 
   no IN
  ( SELECT no FROM dates WHERE date2 BETWEEN '1-JUN-12' AND '30-JUN-12'
  and date3 BETWEEN '1-JUN-12' AND '30-JUN-12' and date1 is null )

  )
    回复于

相关问题