带有几个过滤条件的oracle查询 [英] oracle query with several filter conditions
本文介绍了带有几个过滤条件的oracle查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个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屋!
查看全文