递归查询- Oracle

m1m5dgzv 于 2022-11-03 发布在 Oracle

关注(0)|答案(1)|浏览(260)

我有以下数据和预期结果：

Data                Expected Result 
No  A   B                  A    B
1   10  500               10    500                          
2   10  c=20              20    400
3   20  400               30    600
4   30  600               30    700
5   30  c=40              30    800
6   30  c=50              40    700
7   40  700               50    900
8   50  c=60              60    900
9   60  c=70              70    900
10  70  900               10    400

我需要执行自我链接并取得结果。

对于行号1，预期结果与行相同。
对于第2行，我需要将列B（c=20）的子字符串取为20，并与列B连接，得到结果400。
第5行和第6行需要对列B进行子串处理，并从列A中获取结果。

我尝试了一个递归查询，但仍然没有得到预期的结果。

with rec(A, B, nested) as
(
select A, B, case when instr(B, 'C=') != 0 then substr(B, instr(B, 'C=')) as nested
from table
union all
select A, rec.B from table
inner join rec
on (table.A = rec.nested)
)
select A, B, nested from rec;

oracle

来源：https://stackoverflow.com/questions/74026372/recursive-query-oracle

1条答案

按热度按时间

mpbci0fu1#

initial version of the question的答案

您不需要递归查询。要获得所需的输出，只需排除B以c=开头的行：

SELECT a, b
FROM   table_name
WHERE b NOT LIKE 'c=%';

其中，对于示例数据：

CREATE TABLE table_name (no, a, b) AS
  SELECT 1, 10, '500'  FROM DUAL UNION ALL
  SELECT 2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT 3, 20, '400'  FROM DUAL UNION ALL
  SELECT 4, 30, '600'  FROM DUAL UNION ALL
  SELECT 5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT 6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT 7, 40, '700'  FROM DUAL UNION ALL
  SELECT 8, 50, '800'  FROM DUAL;

输出所需的输出：
| A级|B|
| - -|- -|
| 10个|500个|
| 20个|四百人|
| 30个|六百元|
| 四十个|七百人|
| 五十个|八百元|
fiddle

3rd edit of the question的答案

您可以使用分层查询：

SELECT DISTINCT
       CONNECT_BY_ROOT a AS a,
       b
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR b LIKE 'c=%'
AND    PRIOR SUBSTR(b, 3) = a
ORDER BY a, b;

其中，对于示例数据：

CREATE TABLE table_name (no, a, b) AS
  SELECT  1, 10, '500'  FROM DUAL UNION ALL
  SELECT  2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT  3, 20, '400'  FROM DUAL UNION ALL
  SELECT  4, 30, '600'  FROM DUAL UNION ALL
  SELECT  5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT  6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT  7, 40, '700'  FROM DUAL UNION ALL
  SELECT  8, 50, 'c=60' FROM DUAL UNION ALL
  SELECT  9, 60, 'c=70' FROM DUAL UNION ALL
  SELECT 10, 70, '900'  FROM DUAL;

输出：
| A级|B|
| - -|- -|
| 10个|四百人|
| 10个|500个|
| 20个|四百人|
| 30个|六百元|
| 30个|七百人|
| 30个|九百人|
| 四十个|七百人|
| 五十个|九百人|
| 六十个|九百人|
| 七十个|九百人|
fiddle

赞(0）回复(0）举报 2022-11-03

我来回答

递归查询- Oracle

1条答案

initial version of the question的答案

3rd edit of the question的答案

相关问题

热门标签

最新问答