Oracle SQL左连接同一表的次数未知 [英] Oracle SQL Left join same table unknown amount of times

问题描述

我有这张桌子

| old  | new   | 
|------|-------| 
| a    | b     | 
| b    | c     | 
| d    | e     | 
| ...  | ...   | 
| aa   | bb    | 
| bb   | ff    | 
| ...  | ...   | 
| 11   | 33    | 
| 33   | 523   | 
| 523  | 4444  | 
| 4444 | 21444 | 

我想要达到的结果是

| old  | newest | 
|------|--------| 
| a    | e      | 
| b    | e      | 
| d    | e      | 
| ...  |        | 
| aa   | ff     | 
| bb   | ff     | 
| ...  |        | 
| 11   | 21444  | 
| 33   | 21444  | 
| 523  | 21444  | 
| 4444 | 21444  | 

我可以对查询进行硬编码以获得所需的结果.

I can hard code the query to get the result that I want.

SELECT 
   older.old,
   older.new,
   newer.new firstcol,
   newer1.new secondcol,
   …
   newerX-1.new secondlastcol,
   newerX.new lastcol

from Table older

Left join Table newer 
on older.old = newer.new

Left join Table newer1 
on newer.new = newer1.old

…

Left join Table newerX-1 
on newerX-2.new = newerX-1.old

Left join Table newerX 
on newerX-1.new = newerX.old;

，然后从右边获取第一个不为null的值.

and then just take the first value from the right that is not null.

在这里说明:

| old  | new   | firstcol | secondcol | thirdcol | fourthcol |     | lastcol | 
|------|-------|----------|-----------|----------|-----------|-----|---------| 
| a    | b     | c        | e         | null     | null      | ... | null    | 
| b    | c     | e        | null      | null     | null      | ... | null    | 
| d    | e     | null     | null      | null     | null      | ... | null    | 
| ...  | ...   | ...      | ...       | ...      | ...       | ... | null    | 
| aa   | bb    | ff       | null      | null     | null      | ... | null    | 
| bb   | ff    | null     | null      | null     | null      | ... | null    | 
| ...  | ...   | ...      | ...       | ...      | ...       | ... | null    | 
| 11   | 33    | 523      | 4444      | 21444    | null      | ... | null    | 
| 33   | 523   | 4444     | 21444     | null     | null      | ... | null    | 
| 523  | 4444  | 21444    | null      | null     | null      | ... | null    | 
| 4444 | 21444 | null     | null      | null     | null      | ... | null    | 

问题在于替换链"的长度总是在变化(可以从10到100).

The problem is that the length of "the replacement chain" is always changing (Can vary from 10 to 100).

必须有更好的方法吗?

推荐答案

您正在寻找的是递归查询.像这样:

What you are looking for is a recursive query. Something like this:

with cte (old, new, lev) as
(
  select old, new, 1 as lev from mytable
  union all
  select m.old, cte.new, cte.lev + 1
  from mytable m
  join cte on cte.old = m.new
)
select old, max(new) keep (dense_rank last order by lev) as new
from cte
group by old
order by old;

递归CTE创建所有迭代(您可以通过将查询替换为select * from cte来看到此迭代).在最后一个查询中，我们使用Oracle的KEEP LAST获取每个old的最后一个new.

The recursive CTE creates all iterations (you can see this by replacing the query by select * from cte). And in the final query we get the last new per old with Oracle's KEEP LAST.

Rextester演示: http://rextester.com/CHTG34988

Rextester demo: http://rextester.com/CHTG34988

这篇关于Oracle SQL左连接同一表的次数未知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！