sql case when的使用

最近在sql使用中,发现 case when 的功能相当强大。

可以根据现有字段定义新的字段,可以对新字段进行排序等等。

下面简单举例说明。

用来测试的数据表内容如下:

mysql> select * from test_student;
+----+------+-----+--------+-------+
| id | name | age | sex    | score |
+----+------+-----+--------+-------+
| 19 | John |  18 | male   |    90 |
| 20 | Lily |  17 | female |    89 |
| 21 | Jim  |  17 | male   |    92 |
| 22 | Alex |  16 | male   |    70 |
| 23 | Bell |  19 | ''     |    68 |
+----+------+-----+--------+-------+
5 rows in set (0.00 sec)

例子1

按照性别查询,定义新字段性别

mysql> select id, name, case sex when 'male' then '男' when 'female' then '女' else 'unknown' end as '性别' from test_student;
+----+------+---------+
| id | name | 性别    |
+----+------+---------+
| 19 | John ||
| 20 | Lily ||
| 21 | Jim  ||
| 22 | Alex ||
| 23 | Bell | unknown |
+----+------+---------+
5 rows in set (0.00 sec)

或者使用如下方式:

mysql> select id, name, case when sex = 'male' then '男' when sex = 'female' then '女' else 'unknown' end as '性别' from test_student;
+----+------+---------+
| id | name | 性别    |
+----+------+---------+
| 19 | John ||
| 20 | Lily ||
| 21 | Jim  ||
| 22 | Alex ||
| 23 | Bell | unknown |
+----+------+---------+
5 rows in set (0.00 sec)

例子2

下面的例子中,使用两个字段进行组合case when。

sexmale,并且 score > 90,则加上标签smart boy

sexfemale,并且 score > 90,则加上标签smart girl

其他情况,则为unknown


mysql> select id, name, age, sex, score, case when sex = 'male' and score > 90 then 'smart boy' when sex = 'female' and score > 90 then 'smart girl' else 'unknown' end as 'label' from test_student;
+----+------+-----+--------+-------+-----------+
| id | name | age | sex    | score | label     |
+----+------+-----+--------+-------+-----------+
| 19 | John |  18 | male   |    90 | unknown   |
| 20 | Lily |  17 | female |    89 | unknown   |
| 21 | Jim  |  17 | male   |    92 | smart boy |
| 22 | Alex |  16 | male   |    70 | unknown   |
| 23 | Bell |  19 | ''     |    68 | unknown   |
+----+------+-----+--------+-------+-----------+
5 rows in set (0.00 sec)

例子3

定义新字段,并按照新字段排序。

按照score区间进行划分:

  • score > 90 等级1
  • 80 < score <= 90 等级2
  • 70 < score <= 80 等级3
  • score <= 70 等级4

并按照等级进行排序。

mysql> select id,name,age, sex,score, case when score > 90 then 1 when score > 80 and score <= 90 then 2 when score > 70 and score <= 80 then 3 else 4 end as honor_level from test_student order by honor_level;
+----+------+-----+--------+-------+-------------+
| id | name | age | sex    | score | honor_level |
+----+------+-----+--------+-------+-------------+
| 21 | Jim  |  17 | male   |    92 |           1 |
| 19 | John |  18 | male   |    90 |           2 |
| 20 | Lily |  17 | female |    89 |           2 |
| 22 | Alex |  16 | male   |    70 |           4 |
| 23 | Bell |  19 | ''     |    68 |           4 |
+----+------+-----+--------+-------+-------------+
5 rows in set (0.00 sec)

再例如,

根据sex字段,定义新字段sex_int,并按照sex_int进行排序:

mysql> select *, case when sex = 'male' then 1 when sex = 'female' then 2 else 3 end as sex_int from test_student order by sex_int;
+----+------+-----+--------+-------+---------+
| id | name | age | sex    | score | sex_int |
+----+------+-----+--------+-------+---------+
| 19 | John |  18 | male   |    90 |       1 |
| 21 | Jim  |  17 | male   |    92 |       1 |
| 22 | Alex |  16 | male   |    70 |       1 |
| 20 | Lily |  17 | female |    89 |       2 |
| 23 | Bell |  19 | ''     |    68 |       3 |
+----+------+-----+--------+-------+---------+
5 rows in set (0.00 sec)
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值