oracle如何实现where动态条件（语句最好mysql和oracle通用）

[zznike]

想实现where后面的条件动态化，根据条件不同，where条件也会发现变化，不要用过程方式，以下是mysql的语句，但是在oracle运行不了，请大神看看如何将其转换成oracle和mysql下都能运行的语句： SELECT t.prov_name AS '一级机构', t.city_name AS '二级机构', t.sales_name AS '业务员', t.int_value AS '数量' FROM yl_order_table t WHERE 1 = 1 -- (1)过滤 1级部门 AND CASE WHEN (SELECT COUNT(1) FROM fr_user_city t WHERE t.valid = 'T' AND t.level IN (1,2,3) AND t.user_code = 'username' )<>0 THEN t.prov_name IN (SELECT distinct t.org1_name FROM fr_user_city t WHERE t.valid = 'T' AND t.level IN (1,2,3) AND t.user_code = 'username' ) ELSE 1=1 END -- (2)过滤 2级部门 AND CASE WHEN (SELECT COUNT(1) FROM fr_user_city t WHERE t.valid = 'T' AND t.level IN (2,3) AND t.user_code = 'username' )<>0 THEN t.city_name IN (SELECT distinct t.org2_name FROM fr_user_city t WHERE t.valid = 'T' AND t.level IN (2,3) AND t.user_code = 'username' ) ELSE 1=1 END -- (3)过滤 3级部门 AND CASE WHEN (SELECT COUNT(1) FROM fr_user_city t WHERE t.valid = 'T' AND t.level = 3 AND t.user_code = 'username' )<>0 THEN t.sales_name IN (SELECT distinct t.org3_name FROM fr_user_city t WHERE t.valid = 'T' AND t.level = 3 AND t.user_code = 'username' ) ELSE 1=1 END ;

[nayi_224]

A in (...)被认为是判断表达式而不是返回值，你可以then后再嵌套case when，或者把 t.prov_name IN 写进后面的查询中

[zznike]

引用 1 楼 nayi_224 的回复：

A in (...)被认为是判断表达式而不是返回值，你可以then后再嵌套case when，或者把 t.prov_name IN 写进后面的查询中

我就是要这个in（）作为where的表达式条件，只是根据不同条，where表达式不一样，