想實作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
;
uj5u.com熱心網友回復：
A in (...)被認為是判斷運算式而不是回傳值，你可以then后再嵌套case when，或者把 t.prov_name IN 寫進后面的查詢中uj5u.com熱心網友回復：
我就是要這個in（）作為where的運算式條件，只是根據不同條，where運算式不一樣，轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8484.html
標籤：開發