双重职责问题
问题来自于《SQL puzzles and answers》一书的第36个Puzzle。问题的描述很简单，书中给出了很多种解答，我只能想到其中的1、2种，故在这里进行一下分享。有意思的是书中的解法1是无法通过SQL解析的，大家可以查看原书并进行尝试。本文中的解法在SQL Server 2008中测试通过，可能与原书有部分差异。
问题描述
我们有一张权责表：
| person | role |
| Smith | O |
| Smith | D |
| Jones | O |
| White | D |
| Brown | X |
表中包含2列，person列中存储人名，role列中存储职责代码，O代表Officer，D代表Director等等。创建表的脚本：
use tempdb;
create table Roles
(
person char(5) not null,
role char(1) not null
);
insert into
Roles
(
person,
role
)
values
('Smith', 'O'),
('Smith', 'D'),
('Jones', 'O'),
('White', 'D'),
('Brown', 'X');
需要写一个查询，只关心role为O和D的人。若有人既有职责代码O，又有职责代码D，则合并显示为B。上表的查询结果应为：
| person | combined_role |
| Smith | B |
| Jones | O |
| White | D |
解决方案1
With DirectorRole as
(
select
person,
role
from
Roles
where
role = 'D'
),
OfficerRole as
(
select
person,
role
from
Roles
where
role = 'O'
)
select
coalesce(DirectorRole.person, OfficerRole.person) as person,
case when
(DirectorRole.person is not null and OfficerRole.person is not null)
then
'B'
else
coalesce(DirectorRole.role, OfficerRole.role)
end as combined_role
from
DirectorRole
full outer join
OfficerRole
on
DirectorRole.person = OfficerRole.person;
解决方案2
select
person,
'B' as combined_role
from
Roles
where
role in ('O', 'D')
group by
person
having
COUNT(*) = 2
union all
select
person,
max(role) as combined_role
from
Roles
where
role in ('O', 'D')
group by
person
having
COUNT(*) = 1
解决方案3
select distinct
R1.person,
case when exists(select
*
from
Roles as R2
where
R2.person = R1.person
and
R2.role <> R1.role
and
R2.role in ('D', 'O'))
then 'B'
else R1.role
end as combined_role
from
Roles as R1
where
R1.role in ('O', 'D');
解决方案4
select
person,
case when
COUNT(*) = 1
then
max(role)
else
'B'
end as combined_role
from
Roles
where
role in ('D', 'O')
group by
person;
解决方案5
select
person,
case when MIN(role) <> MAX(role)
then 'B'
else MIN(role)
end as combined_role
from
Roles
where
role in ('D', 'O')
group by
person;
解决方案6
select
person,
SUBSTRING('ODB', sum(charindex(role, 'OD', 1)), 1) as combined_role
from
Roles
where
role in ('D', 'O')
group by
person;
总结
从这个例子里可以充分感受到SQL的丰富性，大家可以进一步比较各方法的性能。第6个方案相对前几个方案难理解一些，我在这里多说两句。此查询首先按照人名来分组（这点很重要）。若某人只有一个O职责，charindex函数将返回1；若某人只有一个D职责，charindex函数将返回2；若某人既有O又有D职责，charindex函数将返回2条记录值分别为1和2，经外层聚合函数sum求和后为3。之后substring再将sum的结果值作为索引取‘ODB’中的一个字母。
0
0