【SQL 學習】case 表示式

SQL> --使用case 表示式

---簡單CASE表示式
SQL> select product_id,product_type_id ,
  2  case product_type_id
  3  when 1 then 'book'
  4  when 2 then 'video'
  5  when 3 then 'dvd'
  6  when 4 then 'cd'
  7  else 'magazine'
  8  end
  9  from products;

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 book
         2               1 book
         3               2 video
         4               2 video
         5               2 video
         6               2 video
         7               3 dvd
         8               3 dvd
         9               4 cd
        10               4 cd
        11               4 cd
        12                 magazine
已選擇12行。

已用時間:  00: 00: 00.03

------搜尋CASE 表示式
SQL> select product_id,product_type_id ,
  2  case
  3  when product_type_id=1 then 'book'
  4  when product_type_id=2 then 'video'
  5  when product_type_id=3 then 'dvd'
  6  when product_type_id=4 then 'cd'
  7  else 'magazine'
  8  end
  9  from products;

PRODUCT_ID PRODUCT_TYPE_ID CASEWHEN
---------- --------------- --------
         1               1 book
         2               1 book
         3               2 video
         4               2 video
         5               2 video
         6               2 video
         7               3 dvd
         8               3 dvd
         9               4 cd
        10               4 cd
        11               4 cd
        12                 magazine
已選擇12行。

------在搜尋CASE表示式中使用運算子。

已用時間:  00: 00: 00.03
SQL> select product_id ,price,
  2  case
  3  when price > 15 then 'Expensive'
  4  else 'cheap'
  5  end
  6  from products;

PRODUCT_ID      PRICE CASEWHENP               
---------- ---------- ---------               
         1      19.95 Expensive
         2         30 Expensive
         3      25.99 Expensive
         4      13.95 cheap
         5      49.99 Expensive
         6      14.95 cheap
         7      13.49 cheap
         8      12.99 cheap
         9      10.99 cheap
        10      15.99 Expensive
        11      14.99 cheap
        12      13.49 cheap

已選擇12行。