我必须加入2个视图view_a和view_b。
View_a具有列ID,地址1,地址2,城市,州,cntryview_b ID,frst_name,last_name,type,date,job_title
所需的结果
ID,名称,地址1,地址2,城市,州,cntry,job_title
我查询的条件是:
1.在ID列上加入两个视图。
2.按日期订购
3. condenate first_name和last_name
4.类型等于"官员"
5.如果有一个以上的军官,则仅屈服一名官员,即基于日期的一排。
6.如果没有官员,则在结果中为名称和job_title列的零值。
查询我使用过:
select
*
from
view_a A
join
(
select
(first_name || ' ' || last_name) as name,
job_title,
id
from
view_b
where
type = 'officer'
and
id is not null
order by date desc fetch first 1 row only
) B
on A.id=B.id
但是此查询仅产生一个结果。我正在使用Oracle 12c。这些视图中大约有800k记录。
您可以做到这一点:
select id,
name,
address1,
address2,
city,
state,
cntry,
job_title
(select
a.id,
nvl2(nvl(b.first_name, b.last_name),b.first_name||' '||b.last_name,null) Name,
a.address1,
a.address2,
a.city,
a.state,
a.cntry,
b.job_title,
a.date
row_number() over (partition by a.id order by a.date desc nulls last) rn
from
view_a a left outer join
view_b b
on a.id = b.id
and b.type = 'officer')
where rn = 1
order by date desc nulls last;
以下也解决了问题:
SELECT *
FROM view_a a
LEFT JOIN (SELECT name, job_title, id
FROM (SELECT (first_name || ' ' || last_name) AS name,
job_title,
id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC) rn
FROM view_b
WHERE TYPE = 'officer' AND id IS NOT NULL)
WHERE rn = 1) b
ON a.id = b.id