双重职责问题

问题来自于《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’中的一个字母。