如何根据学生的电话类型优先级仅选择一行

Question

你好，谢谢你帮我。

一个学生可以有多个电子邮件地址，类型为：个人、工作、其他、学校

我需要写一个查询，为学生选择一个电子邮件地址。如果学生有多个电子邮件地址，则所选的电子邮件地址需要基于电子邮件地址的电子邮件类型。

例如，

• 如果学生有个人电子邮件，那么我只选择个人电子邮件
• 如果学生没有个人电子邮件，但有其他电子邮件类型，那么我会选择他们的学校电子邮件地址

电子邮件类型的优先级顺序为：个人、学校、工作、其他。目标是根据优先级列表(个人、学校、工作，然后是其他(仅选择一个记录

学生表结构

student_id
email_type
email_addr

Answer 1

您可以使用窗口函数和case表达式：

select *
from (
select t.*,
row_number() over(
partition by student_id 
order by case email_type 
when 'personal' then 1
when 'school' then 2
when 'work' then 3
else 4
end 
) rn
from mytable t
) t
where rn = 1

在Oracle中，可以使用decode():缩短此时间

select *
from (
select t.*,
row_number() over(
partition by student_id 
order by decode(email_type, 'personal', 1, 'school', 2, 'work', 3, 4)
) rn
from mytable t
) t
where rn = 1

另一个典型的解决方案是fetch first row with ties:

select t.*
from mytable t
order by row_number() over(
partition by student_id 
order by decode(email_type, 'personal', 1, 'school', 2, 'work', 3, 4)
)
fetch first row with ties

Answer 2

您可以将分析函数与KEEP:一起使用

SELECT student_id,
MAX( email_type ) KEEP (
DENSE_RANK FIRST
ORDER BY DECODE( email_type, 'personal', 1, 'school', 2, 'work', 3, 4 )
) AS email_type,
MAX( email_addr ) KEEP (
DENSE_RANK FIRST
ORDER BY DECODE( email_type, 'personal', 1, 'school', 2, 'work', 3, 4 )
) AS email_addr
FROM   student
GROUP BY student_id

对于一些测试数据：

CREATE TABLE student ( student_id, email_type, email_addr ) AS
SELECT 1, 'school',   'person1@school' FROM DUAL UNION ALL
SELECT 1, 'work',     'person1@work'   FROM DUAL UNION ALL
SELECT 1, 'other',    'person1@other'  FROM DUAL UNION ALL
SELECT 2, 'personal', 'person2@home'   FROM DUAL UNION ALL
SELECT 2, 'other',    'person2@other'  FROM DUAL;

该输出：

STUDENT_ID|EMAIL_TYPE|EMAIL_ADDR---------：|：--------|：-------------1|学校|person1@school2|个人|person2@home

db<gt；小提琴这里