lateral函数oracle,PL/SQL Challenge 每日一题：2019-4-1 Lateral inline view(12C)

(原发表于 2019-2-28)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

注：本题执行环境为12C或更高版本

我有一张用户表，还有一张用户邮箱表：

create table qz_users (

user_id     integer primary key

, user_name   varchar2(20)

)

/

create table qz_emails (

user_id     references qz_users

, priority    integer      not null check (priority in (1, 2, 3))

, validated   varchar2(1)  not null check (validated in ('N', 'Y'))

, email       varchar2(20) not null

, email_type  varchar2(15)

, primary key (user_id, priority)

)

/

insert into qz_users values (68, 'Benjamin')

/

insert into qz_users values (77, 'Brigitte')

/

insert into qz_users values (87, 'Brendan' )

/

insert into qz_users values (96, 'Barbara' )

/

insert into qz_emails values (

68, 1, 'N', 'b.baker@bigcomp.com', 'work'          )

/

insert into qz_emails values (

68, 2, 'Y', 'benjinie@gmail.com' , 'personal'      )

/

insert into qz_emails values (

77, 1, 'N', 'brigidier@yahoo.com', 'personal'      )

/

insert into qz_emails values (

96, 2, 'Y', 'barbieg96@yahoo.com', NULL            )

/

insert into qz_emails values (

96, 3, 'Y', 'barb.young@ngo.org' , 'organisation'  )

/

commit

/

对于一个给定的用户，我将主邮箱定义为：以PRIORITY为顺序，第一个VALIDATED = Y的邮箱。

我想要一个用户列表，包含其主邮箱以及相应的邮箱类别。我不关心没有主邮箱的用户，他们可以出现或者不出现在列表中，这都无关紧要。

哪些选项可以执行不出错，并且返回这两个正确输出的其中一种：

USER_ID USER_NAME            PRIMARY_EMAIL        PRIMARY_TYPE

---------- -------------------- -------------------- ---------------

68 Benjamin             benjinie@gmail.com   personal

96 Barbara              barbieg96@yahoo.com

USER_ID USER_NAME            PRIMARY_EMAIL        PRIMARY_TYPE

---------- -------------------- -------------------- ---------------

68 Benjamin             benjinie@gmail.com   personal

77 Brigitte

87 Brendan

96 Barbara              barbieg96@yahoo.com

(A)

select u.user_id

, u.user_name

, p.email      as primary_email

, p.email_type as primary_type

from qz_users u

cross join lateral (

select e.email, e.email_type

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) p

order by u.user_id

/

(B)

select u.user_id

, u.user_name

, p.email      as primary_email

, p.email_type as primary_type

from qz_users u

join lateral (

select e.email, e.email_type

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) p

on 1 = 1

order by u.user_id

/

(C)

select u.user_id

, u.user_name

, p.email      as primary_email

, p.email_type as primary_type

from qz_users u

join (

select e.user_id, e.email, e.email_type

from qz_emails e

where e.validated = 'Y'

order by e.priority

fetch first 1 row only

) p

on p.user_id = u.user_id

order by u.user_id

/

(D)

select u.user_id

, u.user_name

, (

select e.email

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) as primary_email

, (

select e.email_type

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) as primary_type

from qz_users u

order by u.user_id

/

(E)

select u.user_id

, u.user_name

, (

select e.email, e.email_type

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) as (primary_email, primary_type)

from qz_users u

order by u.user_id

/

(F)

select user_id

, user_name

, substr(email_and_type, 1, instr(email_and_type, '|') - 1)

as primary_email

, substr(email_and_type, instr(email_and_type, '|') + 1)

as primary_type

from (

select u.user_id

, u.user_name

, (

select e.email || '|' || e.email_type

from qz_emails e

where e.user_id = u.user_id

and e.validated = 'Y'

order by e.priority

fetch first 1 row only

) as email_and_type

from qz_users u

)

order by user_id

/

(G)

select u.user_id

, u.user_name

, valid.email      as primary_email

, valid.email_type as primary_type

from qz_users u

join (

select user_id

, email

, email_type

, row_number() over (partition by user_id order by priority) as rn

from qz_emails

where validated = 'Y'

) valid

on valid.user_id = u.user_id

where valid.rn = 1

order by u.user_id

/

(H)

select u.user_id

, u.user_name

, valid.email      as primary_email

, valid.email_type as primary_type

from qz_users u

join (

select user_id

, email

, email_type

, validated

, row_number() over (partition by user_id order by priority) as rn

from qz_emails

) valid

on valid.user_id = u.user_id

where valid.rn = 1

and valid.validated = 'Y'

order by u.user_id

/

(I)

select u.user_id

, u.user_name

, valid.email      as primary_email

, valid.email_type as primary_type

from qz_users u

join (

select user_id

, max(email)      keep (dense_rank first order by priority)

as email

, max(email_type) keep (dense_rank first order by priority)

as email_type

from qz_emails

where validated = 'Y'

group by user_id

) valid

on valid.user_id = u.user_id

order by u.user_id

/

(J)

select u.user_id

, u.user_name

, valid.email      as primary_email

, valid.email_type as primary_type

from qz_users u

join (

select user_id

, max(email)      keep (dense_rank first order by validated)

as email

, max(email_type) keep (dense_rank first order by validated)

as email_type

from qz_emails

where validated = 'Y'

group by user_id

) valid

on valid.user_id = u.user_id

order by u.user_id

/