Simple CASE Example For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers;CUST_LAST_NAME CASECR -------------------- ------ ... Bogart Medium Nolte Medium Loren Medium Gueney MediumSearched CASE Example The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" from employees e;Average Salary -------------- 6461.68224
试一下,没测试过 select case kind=10 when then (select ta.id from ta,ma where ta.kind=10 and ta.no=ma.no) else (select ta.id from ta,mb where ta.kind=20 and ta.no=mb.no) end from tA;
For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.SELECT cust_last_name,
CASE credit_limit WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium' END
FROM customers;CUST_LAST_NAME CASECR
-------------------- ------
...
Bogart Medium
Nolte Medium
Loren Medium
Gueney MediumSearched CASE Example
The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000 END) "Average Salary" from employees e;Average Salary
--------------
6461.68224
select case kind=10 when then (select ta.id from ta,ma where ta.kind=10 and ta.no=ma.no)
else (select ta.id from ta,mb where ta.kind=20 and ta.no=mb.no) end from tA;
可以用在WHERE子句里吗
但是如果原来有100行SQL文
UNION一下就200行
如果有10种情况,则就是1000行,
感觉不是很好,
不知道能不能在WHERE里解决
select ta.*,decode(id,10,select name from ma where ta.no=ma.no,
20,select name from mb where ta.no=mab.no,null) name from ta;
(select no from MA where t.no=MA.no) a,
(select no from MB where t.no=MB.no) b;楼主还想追求到何种完美方才罢休?
SELECT TA.ID,
TA.kind,
TA.no,
DECODE(TA.kind,10,MA.NAME,20,MB.NAME,'')
FORM TA,
MA,
MB
WHERE TA.no = MA.no(+)
AND TA.no = MB.no(+)