主页 / dba / 问题 / 87355
Accepted
Kyle Decot
Asked: 2015-01-01 10:41:56 +0800 CST

避免使用多个“或”表达式

  • 772

我有以下 oracle SQL 及其作品，但所有ors 都很难看。有没有更简洁的方法来做到这一点？

SELECT * FROM foobar WHERE
  (SUBJECT ='STAT' and TERM ='111') or  
  (SUBJECT ='STAT' and TERM ='222') or  
  (SUBJECT ='ENGLISH' and TERM ='555') or 
  (SUBJECT ='COMM' and TERM ='444') or
  (SUBJECT ='COMM' and TERM ='333') or  
  (SUBJECT ='STAT' and TERM ='666')
  ...
oracle query-refactor

3 个回答

  1. Best Answer

    你可能更喜欢这样的东西：

    select *
from foobar
where (subject,term) in ( ('STAT','111')
                         ,('STAT','222')
                         ,('ENGLISH','555')
                         ,('COMM','444')
                         ,('COMM','333')
                         ,('STAT','222')
                         ,('STAT','666') 
                        );

    DBFiddle在这里

    • 21

  2. 就纯代码清理而言，以下内容看起来更简洁：

    SELECT * 
  FROM foobar 
  WHERE (SUBJECT = 'STAT' and TERM IN ('111','222','666') )
    OR  (SUBJECT = 'COMM' and TERM IN ('333','444') )
    OR  (SUBJECT = 'ENGLISH' and TERM = '555' ) ;

    根据应用程序以及重用逻辑的频率，还可能值得设置一个查找表来应用逻辑：

    CREATE TABLE foobar_lookup (SUBJECT VARCHAR2(7), TERM VARCHAR2(3)) ;

INSERT INTO foobar_lookup SELECT 'STAT',    '111' FROM dual ;
INSERT INTO foobar_lookup SELECT 'STAT',    '222' FROM dual ;
INSERT INTO foobar_lookup SELECT 'STAT',    '666' FROM dual ;
INSERT INTO foobar_lookup SELECT 'COMM',    '444' FROM dual ;
INSERT INTO foobar_lookup SELECT 'COMM',    '333' FROM dual ;
INSERT INTO foobar_lookup SELECT 'ENGLISH', '555' FROM dual ;

SELECT f.* FROM foobar f
JOIN foobar_lookup fl 
    ON fl.subject = f.subject
    AND fl.term = f.term ;
    • 11

  3. 这是另一种方法。使用 where (col1,col2) 可能会导致 Oracle 不使用任何索引，但这看起来像查询的表，因此它可能会更好地工作。一旦您测试了各种版本，您就会知道。

      WITH subject_terms 
            (subject,   term) AS
    ( SELECT 'STAT'   , '111' FROM dual UNION ALL
      SELECT 'STAT'   , '222' FROM dual UNION ALL
      SELECT 'ENGLISH', '555' FROM dual UNION ALL
      SELECT 'COMM'   , '444' FROM dual UNION ALL
      SELECT 'COMM'   , '333' FROM dual UNION ALL
      SELECT 'STAT'   , '666' FROM dual )
SELECT * 
  FROM foobar             fb
 INNER JOIN subject_terms st
    ON fb.subject = st.subject
   AND fb.term    = st.term;

    DBFiddle在这里

    • 4

相关问题