幾個sql語句中的case使用

表結構

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | NO   | PRI |         |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| sex   | char(1)     | YES  |     | NULL    |       |

| addr  | varchar(50) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

 

1. select id, name

,(case sex when 'm' then '男' when 'f' then '女' else '其他' end) "性別"

from stu;

結果:

+------+----------+------+

| id   | name     | 性別 |

+------+----------+------+

| 1101 | andylin  | 男     |

| 1102 | congfeng | 男     |

| 1103 | lihong   | 女    |

+------+----------+------+

 

2.  select id, name

, (case sex when 'm' then '男' end) "male"

, (case sex when 'f' then '女' end) "female"

from stu;

結果:

+------+----------+------+--------+

| id   | name     | male | female |

+------+----------+------+--------+

| 1101 | andylin  | 男     | NULL   |

| 1102 | congfeng | 男     | NULL   |

| 1103 | lihong   | NULL | 女      |

+------+----------+------+--------+