带有几个过滤条件的oracle查询 [英] oracle query with several filter conditions

问题描述

我有一个oracle查询:

I have a oracle query:

select * from table1,table2 where table1.id1 = table2.id2 and 
--filters
table1.col2 = 'value_11' and table2.col2 = 'value_21'
or
table1.col2 = 'value_12' and table2.col2 = 'value_22'
or
table1.col2 = 'value_13' and table2.col2 = 'value_23'
...

如果过滤条件达到100，如何重写查询?就像(value_11,value_21),(value_12,value_22)......(value_1100,value_2200)

How to rewrite the query if the filter conditions run upto a 100? like (value_11,value_21),(value_12,value_22)......(value_1100,value_2200)

原始查询是这样:

select 
    cust_country.cust_login_id 
  , cust_country.user_login_id
  , cust_country.country_name
  , cust_email.email 
from ( select 
           APP_USER.USER_ID, customer.cust_login_id
         , user_credential.user_login_id, COUNTRY.COUNTRY_NAME 
       from 
           app_user
         , user_ci
         , contact_info
         , address
         , country
         , customer
         , user_credential 
       where APP_USER.USER_ID = USER_CI.USER_ID 
         and CONTACT_INFO.CI_ID=USER_CI.CI_ID
         and ADDRESS.ADDR_ID=CONTACT_INFO.CI_ID 
         and ADDRESS.COUNTRY_ID=COUNTRY.COUNTRY_ID 
         and APP_USER.CUST_ID=CUSTOMER.CUST_ID 
         and USER_CREDENTIAL.USER_ID=APP_USER.USER_ID
     ) cust_country,

     ( select 
           customer.cust_login_id, APP_USER.USER_ID
         , user_credential.user_login_id, ipty_email.email
       from 
         app_user,user_ci,contact_info,ipty_email,customer,user_credential 
       where APP_USER.USER_ID = USER_CI.USER_ID 
         and CONTACT_INFO.CI_ID=USER_CI.CI_ID 
         and ipty_email.ipe_ID=CONTACT_INFO.CI_ID 
         and APP_USER.CUST_ID=CUSTOMER.CUST_ID 
         and USER_CREDENTIAL.USER_ID=APP_USER.USER_ID
     ) cust_email

where cust_country.user_id = cust_email.user_id 
  and (cust_country.cust_login_id, cust_country.user_login_id) in 
      (('xxxxyyyyy','ADMIN2'))

推荐答案

您可以使用以下方法(一点)对其进行压缩:

You could compress it (a bit) with:

SELECT *                                       --- irrelevant to the question:
FROM table1 JOIN table2                        --- use the explicit JOIN syntax
             ON table1.id1 = table2.id2        --- not the implicit join with the
---table1,table2 where table1.id1 = table2.id2 --- WHERE syntax (removed)
WHERE
--- filters
  AND (table1.col2, table2.col2) IN
        (  ('value_11', 'value_21'),
           ('value_12', 'value_22'),
           ('value_13', 'value_23'),
           ...
           (value_1100, value_2200)
        ) 

如果表中有这些过滤条件，您甚至可以做到:

If you have these filter conditions in a table, you can even make it:

  AND (table1.col2, table2.col2) IN
        (  SELECT filter1, filter2
           FROM filter_table
        )

这篇关于带有几个过滤条件的oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！