select col,case col1 when(col1=1 then ... else....end) from tab 要用动态的sql
Oracle8.1.7没有case功能的,只能用decode语句,要oracle9i以上可以用
8i中已经支持case语句了 12:25:19 SQL> select * from v$version;BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production PL/SQL Release 8.1.6.0.0 - Production CORE 8.1.6.0.0 Production TNS for 32-bit Windows: Version 8.1.6.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production已用时间: 00: 00: 00.00 12:25:48 SQL> select * from t1; A ---------- 1 2 3 4 5已用时间: 00: 00: 00.00 12:25:52 SQL> select case when a=1 then 0 when a=2 then -1 else a end from t1;CASEWHENA=1THEN0WHENA=2THEN-1ELSEAEND ------------------------------------- 0 -1 3 4 5已用时间: 00: 00: 00.00
oracle8.1.7支持case的,俺现在的就是8.1.7
select case when name='1' then 1 else 2 end from table
要用动态的sql
12:25:19 SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production已用时间: 00: 00: 00.00
12:25:48 SQL> select * from t1; A
----------
1
2
3
4
5已用时间: 00: 00: 00.00
12:25:52 SQL> select case when a=1 then 0 when a=2 then -1 else a end from t1;CASEWHENA=1THEN0WHENA=2THEN-1ELSEAEND
-------------------------------------
0
-1
3
4
5已用时间: 00: 00: 00.00