9.13. 溇傽臘膆嚟

9.13.1. CASE

CASE 臘膆嚟滇婔䓉锔䫘䔇溇傽臘膆嚟䌂嚚庯噽垄臺蘔婺䔇 if/else 臺埖㔗

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

CASE 床埖埇傖䫘庯傂嘘臘膆嚟埇傖庻婘䔇婄桹㔗condition 滇婔婻誫啂 boolean 䔇臘膆嚟㔗套悩䂷悩婺䩘闼幽 CASE 臘膆嚟䔇䂷悩儌滇严劽溇傽䔇 result 㔗套悩䂷悩婺啺闼幽傖䕩劯桹嚟抩凂傂嘘锟劯䔇 WHEN 床埖㔗套悩澇橬 WHEN condition 婺䩘闼幽臘膆嚟䔇䂷悩儌滇婘 ELSE 床埖麯䔇 result 㔗套悩䩕䘖庖 ELSE 床埖婫澇橬对陉䔇溇傽䂷悩婺 NULL 㔗

婔婻冋床

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

欔橬 result 臘膆嚟䔇昄扞䔇䌂傋鄘媙釂埇傖蘸扵潊剘婔䔇膷庺䌂傋㔗埗黙誗10.5诙埡䂖誗㔗

婋麵認婻"䞔剘䔇" CASE 臘膆嚟滇婪麵䔇锔䫘嘵嚟䔇婔婻䬹枪䔇埻䓉

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

噽螇䞖 expression 䔇唚䇽劯婯欔橬 WHEN 床埖麯弄滯䔇 value 凹懫䕘彄欆彄婔婻䕩京䔇㔗套悩澇橬欆彄对陉䔇彍誫啂婘 ELSE 床埖麯䔇 result(潡蔙 NULL)㔗認婻䌂嚚庯 C 麯䔇 switch 臺埖㔗

婪麵䔇冋床埇傖䫘䞔剘 CASE 臺濘準喍

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

CASE 臘膆嚟幽婉螇䞖傂嘘凹庯彴桺䂷悩幽婉驔襕䔇床臘膆嚟㔗懫套婋麵滇婔婻埇傖镪噉赆镽鍴䔇桹濘

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

9.13.2. COALESCE

COALESCE(value [, ...])

COALESCE 誫啂垄䔇丸婔婻麂 NULL 䔇埗昄唚㔗垄婩䫘庯婘滆䴺昄扞施䫘䚺䩕唚敪扵 NULL 㔗懫套

SELECT COALESCE(description, short_description, '(none)') ...

启 CASE 臘膆嚟婔湙COALESCE 婉嚔螇䞖婉驔襕䫘準彴桺䂷悩䔇埗昄幘儌滇臘婘丸婔婻麂䷺埗昄埿膹䔇埗昄婉嚔赆螇䞖㔗認婻严劽 SQL 湺庖䔇庘昄柊冕庖婯昊底噽垄昄扞康係䂘婺䔇 NVL 启 IFNULL 䌂嚚䔇媘脘㔗

9.13.3. NULLIF

NULLIF(value1, value2)

嘷婫備嘷 value1 启 value2 䕩京施NULLIF 欉誫啂 NULL 㔗劥彍垄誫啂 value1 㔗認底埇傖䫘庯欓臯婪麵䂍庺䔇 COALESCE 冋床䔇埉冋

SELECT NULLIF(value, '(none)') ...

套悩 value1 滇 (none) 闼幽誫啂 NULL 劥彍誫啂 value1 㔗

9.13.4. GREATEST 启 LEAST

GREATEST(value [, ...])

LEAST(value [, ...])

GREATEST 启 LEAST 庘昄傯婔婻傂懟昄庖臘膆嚟䔇彖臘麯锬埡橔崓潡蔙橔償䔇昄唚㔗認底臘膆嚟媙釂鄘埇傖蘸扵潊婔婻捞锔䔇昄扞䌂傋垄儖嚔滇䂷悩䌂傋(埗黙誗10.5诙埡䂖誗)㔗彖臘婺䔇 NULL 唚儖赆媘䘖㔗埻橬欔橬臘膆嚟䔇䂷悩鄘滇 NULL 䔇施唍䂷悩欉嚔滇 NULL 㔗

臙濘懟 GREATEST 启 LEAST 鄘婉滇 SQL 湺庖嘖剘滇冽婩蓕䔇欷匘㔗