ORACLE 习题之二

习题二
Northwind商贸公司，业务日益发展，公司OA系统正不断推出新版本以紧跟公司的发展.
在OA系统中，有一员工角色表，情况如下：
create table roles(
emp_name varchar2(20) not null,
emp_role char(1) not null,
constraint pk_roles primary key(emp_name,emp_role)
);
数据：
EMP_NAME EMP_ROLE
-------------------- --------
陈城 W
刘海 D
刘海 O
田亮 O
王晓刚 D
张玲 S
张天明 D
张天明 O

其中： W – 搬运工人 D – 主任 O – 高级职员 S – 秘书

OA开发组的SQL程序员张明得到了上级的一个任务：

领导要求得到的高级职员信息表如下：
EMP_NAME COMBINE_ROLE
-------------------- ------------
刘海 B
田亮 O
王晓刚 D
张天明 B

要求：

1）只列出主任和高级职员的信息
2）如果即是高级职员又是主任，用B表示其角色, 其它信息不用再显示 （只一条记录）。
你能不能用单条SQL语句帮助张明实现这个查询？

建表和数据录入：
create table roles(
emp_name varchar2(20) not null,
emp_role char(1) not null,
constraint PK_ROLES PRIMARY KEY (emp_name,emp_role)
);

insert into roles values('mary','W');
insert into roles values('david','D');
insert into roles values('david','O');
insert into roles values('henry','O');
insert into roles values('cherry','D');
insert into roles values('sally','S');
insert into roles values('tom','D');
insert into roles values('tom','O');
解题思路：

方法1：

先找出角色为主任和高级职员的员工，然后按员工姓名进行分组，得出有的组有2条记录，而有的组只有1条记录，接着把有2条记录的组，角色一律设置为B，对于只有一条记录，则按照其原有设置显示，但由于emp_role不是排序字段，所以增加个聚合函数max, 以保证兼容性，最后把两次查询得到的结果用union合成。

select emp_name,'B'
from roles
where emp_role in ('D','O')
group by emp_name
having count(emp_role)=2
union
select emp_name,max(emp_role)
from roles
where emp_role in ('D','O')
group by emp_name
having count(emp_role)=1;

方法二：

使用case函数。

select emp_name,
case when count(*)=1
then max(emp_role)
else 'B'
end
as combind_role--别名
from roles
where emp_role in ('D','O')
group by emp_name;