著作权
备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
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)
技术文档
热门文章
155-2731-8020