如何构造SQL子查询？

select
e.EMPNO,
e.ENAME
from  
emp e
where 
e.DEPTNO in  (select case
                      when 'D'='D'  then q'[10,20]'
                       END DPT 
                  from dual )

Edit:

the real query is something like

真正的查询是这样的

SELECT e.name, e.entid
  FROM ent e
 WHERE     e.etype = 'DPT'
       AND e.entid IN (
                        SELECT CASE
                                WHEN :P96_GP_TYPE = 'R' THEN q'['QUA','SAMP']'
                                WHEN :P96_GP_TYPE = 'REWR' THEN q'['WASH','FNISH']' END
                        FROM DUAL
                      )

1 个解决方案

#1

You don't need a subquery for this, but you can better use a bit of boolean logic:

您不需要子查询,但您可以更好地使用一些布尔逻辑:

SELECT e.name, e.entid
  FROM ent e
 WHERE     e.etype = 'DPT'
       AND (
                (:P96_GP_TYPE = 'R' AND e.entid IN ('QUA','SAMP') )
                 OR
                (:P96_GP_TYPE = 'REWR'  AND e.entid IN ('WASH','FNISH') )
           )

To clarify where your approach fails, consider that these three queries are fully equivalent:

要澄清您的方法失败的位置,请考虑这三个查询是完全等效的:

select *
from someTable
where someColumn in (  q'['X','Y']' )           

select *
from someTable
where someColumn in ( 
                     select q'['X','Y']'
                     from dual 
                    )

select *
from someTable
where someColumn in ( 
                     select '''X'',''Y'''
                     from dual 
                    )

If you want to use a subquery to get a list of values, not a literal that seems like a list of values, you need something like:

如果要使用子查询来获取值列表,而不是看起来像值列表的文字,则需要以下内容:

select *
from someTable
where someColumn in (
                        select 'X'
                        from dual
                        UNION ALL
                        select 'Y'
                        from dual 
                    )

#1