MySQL 使用CASE表达式给行做标记

谁借莪１个温暖的怀抱￠ 2022-10-28 01:30 162阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

把一列中的值(如EMP表中的JOB列)映射成一列“布尔”标记。

例如，希望如下返回:  
±-------±---------±---------±-------±-----------±--------+  
| ename | is\_clerk | is\_sales | is\_mgr | is\_analyst | is\_prez |  
±-------±---------±---------±-------±-----------±--------+  
| KING | 0 | 0 | 0 | 0 | 1 |  
| SCOTT | 0 | 0 | 0 | 1 | 0 |  
| FORD | 0 | 0 | 0 | 1 | 0 |  
| JONES | 0 | 0 | 1 | 0 | 0 |  
| BLAKE | 0 | 0 | 1 | 0 | 0 |  
| CLARK | 0 | 0 | 1 | 0 | 0 |  
| ALLEN | 0 | 1 | 0 | 0 | 0 |  
| WARD | 0 | 1 | 0 | 0 | 0 |  
| MARTIN | 0 | 1 | 0 | 0 | 0 |  
| TURNER | 0 | 1 | 0 | 0 | 0 |  
| SMITH | 1 | 0 | 0 | 0 | 0 |  
| ADAMS | 1 | 0 | 0 | 0 | 0 |  
| JAMES | 1 | 0 | 0 | 0 | 0 |  
| MILLER | 1 | 0 | 0 | 0 | 0 |  
±-------±---------±---------±-------±-----------±--------+

这样的结果集可用于调试，它能够提供一个不同于其他典型结果集的数据视图。

# 二.解决方案 #

对每个雇员的JOB使用CASE表达式,并返回1或0表示他的JOB。  
需要为每个可能的职位写一个CASE表达式，并创建一列:

select  ename,
            case when job = 'CLERK'
                 then 1 else 0
            end as is_clerk,
            case when job = 'SALESMAN'
                 then 1 else 0
            end as is_sales,
            case when job = 'MANAGER'
                 then 1 else 0
            end as is_mgr,
            case when job = 'ANALYST'
                 then 1 else 0
            end as is_analyst,
            case when job = 'PRESIDENT'
                 then 1 else 0
            end as is_prez
      from  emp
     order  by 2,3,4,5,6;

测试记录:

mysql> select  ename,
        ->         case when job = 'CLERK'
        ->              then 1 else 0
        ->         end as is_clerk,
        ->         case when job = 'SALESMAN'
        ->              then 1 else 0
        ->         end as is_sales,
        ->         case when job = 'MANAGER'
        ->              then 1 else 0
        ->         end as is_mgr,
        ->         case when job = 'ANALYST'
        ->              then 1 else 0
        ->         end as is_analyst,
        ->         case when job = 'PRESIDENT'
        ->              then 1 else 0
        ->         end as is_prez
        ->   from  emp
        ->  order  by 2,3,4,5,6
        -> ;
    +--------+----------+----------+--------+------------+---------+
    | ename  | is_clerk | is_sales | is_mgr | is_analyst | is_prez |
    +--------+----------+----------+--------+------------+---------+
    | KING   |        0 |        0 |      0 |          0 |       1 |
    | SCOTT  |        0 |        0 |      0 |          1 |       0 |
    | FORD   |        0 |        0 |      0 |          1 |       0 |
    | JONES  |        0 |        0 |      1 |          0 |       0 |
    | BLAKE  |        0 |        0 |      1 |          0 |       0 |
    | CLARK  |        0 |        0 |      1 |          0 |       0 |
    | ALLEN  |        0 |        1 |      0 |          0 |       0 |
    | WARD   |        0 |        1 |      0 |          0 |       0 |
    | MARTIN |        0 |        1 |      0 |          0 |       0 |
    | TURNER |        0 |        1 |      0 |          0 |       0 |
    | SMITH  |        1 |        0 |      0 |          0 |       0 |
    | ADAMS  |        1 |        0 |      0 |          0 |       0 |
    | JAMES  |        1 |        0 |      0 |          0 |       0 |
    | MILLER |        1 |        0 |      0 |          0 |       0 |
    +--------+----------+----------+--------+------------+---------+
    14 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb