PL-SQL - 一对多关系中的第一条记录left join
我试图在Oracle中使用LEFT JOIN连接两个表。我只需要包含“正确”连接表中的第一条记录。PL-SQL - 一对多关系中的第一条记录left join
见下面的例子:
表A
code | emp_no
101 | 11111
102 | 22222
103 | 33333
104 | 44444
105 | 55555
表B
code | city | county
101 | City1 | Country1
101 | City2 | Country1
101 | City3 | Country1
102 | City4 | Country2
103 | City5 | Country3
预期输出:
code | emp_no | city | county
101 | 11111 | City1 | Country1
102 | 22222 | City4 | Country2
103 | 33333 | City5 | Country3
104 | 44444 | NULL | NULL
105 | 55555 | NULL | NULL
我需要从表B中选择第一个匹配的记录并忽略所有其他行。
上面的查询猜想作品:
SELECT *
FROM TABLE_A a
LEFT JOIN TABLE_B b ON b.CODE = a.CODE
AND b.CODE =
(
SELECT CODE
FROM TABLE_B
WHERE ROWNUM = 1
)
但我得到的错误: ORA-01799:一列可能不是外连接到一个子查询
我该怎么办这个?使用ROW_NUMBER()函数
感谢
可以使用min()
aggrenate功能与the keep (dense_rank first ...)
syntax从外连接表得到“第一”的匹配数据:
select a.code, a.emp_no,
min(b.city) keep (dense_rank first order by city, county) as city,
min(b.county) keep (dense_rank first order by city, county) as county
from table_a a
left join table_b b on b.code = a.code
group by a.code, a.emp_no
order by a.code, a.emp_no;
CODE EMP_NO CITY COUNTY
---------- ---------- ----- --------
101 11111 City1 Country1
102 22222 City4 Country2
103 33333 City5 Country3
104 44444
105 55555
你必须定义什么“第一”的意思,但 - 我已在keep条款中按顺序排列city, county
,但您可能有另一个列没有显示应该规定订单。
(您可以为了通过空使之显得有些武断,但是这并不是一个好主意,尤其是更高版本上运行相同的查询可能会给出同样的数据不同的结果。)
谢谢亚历克斯,工作! 只是为了澄清,我使用BIRT(PL-SQL集成)和我的真实情况,它比这更复杂。 1.我的“第一”行是基于最近更改的(LST_UPD_TSTMP) 2.此问题将与当前包含用户信息的现有报告(如“ID,名称...”一起加入) 3. TABLE_B基本上包含: STUD_ID(PK,FK); DEL_STUD_ID(获得用户权限的用户)(PK,FK); LST_UPD_TSTMP(NOT NULL) 4.此后,我需要再次加入以提供有关DEL_STUD_ID的信息 我会尝试在我的报告中使用此查询并在此处更新可以吗? –
@IgorIvanov - 然后听起来像你需要在'keep'子句中使用'order by lst_upd_tstmp desc'。 (或者,如果你认为它更好地使用'last'而不是'first',所以你不需要'desc':即'max(b.city)保持(由last_upd_tstmp的最后一个顺序的dense_rank)'等) –
Alex Poole,谢谢!它按预期工作! –
,并得到记录,其中row_number()
= 1
SELECT select a.code,
a.emp_no,
b.city,
b.county
FROM table_a a
left join (SELECT code,
city,
county,
row_number()
over (
PARTITION BY code
ORDER BY city, county) rn
FROM table_b) b
ON b.code = a.code
WHERE rn = 1 OR rn IS NULL;
注:它仍然是从什么的问题实际上这意味着目前还不清楚。
first record from the "right" joined table
目前只给出'a'中一个代码的'b.code'值,并且您已经拥有了这个值;它不会给你任何行的城市/县? –
我的第一条记录是基于LST_UPD_TSTMP(真实情况)。我不明白我应该在** id **:'PARTITION BY id'中使用什么。 –
@IgorIvanov - 看起来你会使用'PARTITION BY code'。但目前不会显示104或105行;你可以将最后一行改为'WHERE rn IS NULL OR rn = 1'来修正... –
在甲骨文12c你可以使用OUTER APPLY
和FETCH FIRST
条款:
SELECT *
FROM tableA a
OUTER APPLY (
SELECT * FROM tableB b
WHERE a.code = b.code
ORDER BY city, county
FETCH FIRST ROW ONLY
)
CODE EMP_NO CODE CITY COUNTY
---------- ---------- ---------- ----- --------
101 11111 101 City1 Country1
102 22222 102 City4 Country2
103 33333 103 City5 Country3
104 44444
105 55555
谢谢,这很简单!但它是11g。我们可以在11g上使用类似的东西吗? –
你是如何决定哪个是'第一'? –
注意:目前尚不清楚你想通过只检索'ROWNUM = 1'来实现。无论如何,我已经写了答案。 –