Oracle CASE WHEN用法介绍1 s( S6 F( G+ \5 `# Q! X! d4 U
$ f' |( g4 _- G" E, o
1. CASE WHEN 表达式有两种形式
8 v ^0 {) `( A# j8 Z& m: n
9 z1 p, p' {/ x" n8 u --简单Case函数, e* o9 S8 @: M7 O
& J3 w! F. N" c+ @$ j/ j7 w) a# C CASE sex
. a, @3 n) }2 q3 k3 c% [* @
/ d$ a8 Y4 A* F( y" \. d( S. I# C WHEN '1' THEN '男'5 |" y; f% T D% E9 G2 \$ c* [- X
% i N1 A5 Z; |; J WHEN '2' THEN '女'
* y" D! ?5 A6 Y4 Y" Z/ I+ K 7 ]% \! g4 j9 p% T/ L# J
ELSE '其他' END+ x4 s3 ]$ g( e( P3 G* a3 |/ `
1 W# D/ _8 v! ?$ l" K- { --Case搜索函数
3 I- U/ v; j6 T( \, s) K . f" n: Y9 c: O/ l$ t
CASE+ f9 ~& Z. P0 z+ h, q8 h) V' W
* A$ _. ?0 }, }# s$ e
WHEN sex = '1' THEN '男'
! H0 m7 N3 I4 |3 Z
" [8 }) W% B5 D6 r WHEN sex = '2' THEN '女'
" t7 x- G5 \7 J* R1 U" Q& [ : K7 X+ g! z. p+ E$ E
ELSE '其他' END! U: Q9 c1 W/ J1 V e& D
3 H2 i8 k# v: F _
2. CASE WHEN 在语句中不同位置的用法 g& i n5 N# r& t" C2 g
7 G+ t4 D6 h. q0 R
2.1 SELECT CASE WHEN 用法
* d" ~! W7 A* D/ N6 W
5 Z) L3 c7 j( F; d2 p7 U5 v8 n+ u SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/
4 B& l3 p8 |( f
- t( L P2 @" F- ~8 { ELSE NULL3 M$ P# W+ I2 V" Q
# z8 I& e `9 U( ~ END) 男生数,3 E! x6 W5 `: }5 a7 u$ Z
. B8 G' X9 q: m/ K, Q, i" v COUNT (CASE WHEN sex = 2 THEN 13 @+ j8 M) y2 q: g7 B
7 A4 N I( s" u' r7 }
ELSE NULL, m3 K* v# Y2 V/ n, a. X$ J
0 \" h; | V; D5 `
END) 女生数( h/ B. Y9 [* Y
, e1 a4 m! U+ n0 w/ q0 c3 _
FROM students GROUP BY grade;
% O4 A+ N* D* [, F+ z 8 s2 ?* K4 Q0 I& ~) j
2.3 WHERE CASE WHEN 用法
0 W- `5 ]7 I* S; N: {
" L- h$ D! ~8 E/ u5 w& Z3 _" s( } SELECT T2.*, T1.*+ ]# F0 E7 ]. _( ?9 o) C
7 U' D) B+ E& \' D FROM T1, T2% l$ i' S( k8 A7 r& Y
' l- L6 Y X5 o7 C8 i% h9 X6 C WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
" Z5 |: f( }+ a" {! G' D# r+ B 3 V3 w# N; c4 A" O& U1 Z
T1.SOME_TYPE LIKE 'NOTHING%'
2 K2 F! J/ S) K) M- e& I: v
1 H3 ^& i) s9 P) j' @- {6 X- R THEN 16 v! i: |) g1 U/ r8 O/ F6 k% F( P
m" ]7 x6 o( I) q4 D* }& L7 z WHEN T2.COMPARE_TYPE != 'A' AND. ?* O; @; t, b- z: S
# P( C8 R& c9 i3 u4 _
T1.SOME_TYPE NOT LIKE 'NOTHING%'( a8 z7 e O% V: B
2 k( k8 k9 r% q THEN 11 F2 | B3 R! f% T& j7 a9 J
) u1 [, E9 ^- Z) }. W0 `+ ]: m$ l
ELSE 0
7 e: h1 j" o2 ? e 1 K8 x" v( p n, M9 F4 j
END) = 1 |