mysql case when使用者樣本

標籤：mysq   select case   case when   

看到一些朋友在使用bind-dlz時寫的mysql語句一知半解，索性自己動手寫幾個樣本實驗一下，加深印象，廢話不多說，直解上料：

1）原始記錄

mysql> select id,name from test;
+------+------+
| id   | name |
+------+------+
|    1 | lili |
|   10 | yaya |
|   30 | cici |
+------+------+

2）文法
CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END


CASE   
WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

3）樣本
--------------  樣本 -----------------------
3.1樣本1

 mysql>  select name,  
    ->      case   
    ->             when id<‘9‘ then ‘yong‘  
    ->             when id>‘15‘ then ‘old‘  
    ->             else ‘ok‘ END YORN  
    ->     from test;
+------+------+
| name | YORN |
+------+------+
| lili | yong |
| yaya | ok   |
| cici | old  |
+------+------+
3.2樣本2

#當end後面沒有用於該列的簡寫時
mysql> select name,  
    ->        case   
    ->                when id<‘9‘ then ‘yong‘
    ->                when id>‘15‘ then ‘old‘
    ->                else ‘ok‘ END   
    ->        from test;

+------+---------------------------------------------------------------------------------------------------------------------+
| name | case   
               when id<‘9‘ then ‘yong‘
               when id>‘15‘ then ‘old‘
               else ‘ok‘ END |
+------+---------------------------------------------------------------------------------------------------------------------+
| lili | yong                                                                                                                |
| yaya | ok                                                                                                                  |
| cici | old                                                                                                                 |
+------+---------------------------------------------------------------------------------------------------------------------+

3.3樣本2

#任意列都可以為when的值，如id
mysql> select name,  
       case   
               when id<‘9‘ then ‘yong‘
               when id>‘15‘ then id
               else ‘ok‘ END yarn  
       from test;

+------+------+
| name | yarn |
+------+------+
| lili | yong |
| yaya | 10   |
| cici | old  |
+------+------+

本文出自 “風雲的部落格” 部落格，請務必保留此出處http://cwind.blog.51cto.com/62582/1605209

mysql case when使用者樣本