在oracle中递归查询

debugcn 发表于 Dev

阿克舒比娅

我在Oracle中有两个表来获取软件版本

RequiredVersion Table
major minor maintenance requiredversion
20      0      0         20.0.1
20      0      1         20.0.3
20      0      3          null
20      0      4          null
20      0      2         20.0.5
20      0      5          null
20      0      6          null

OptimumVersion Table
major minor maintenance optimumver
20      0       0          20.0.2
20      0       2          20.0.6
20      0       1          20.0.4

用户将发送我正在拆分的版本的输入20.0.0，并与两个表中的主要次要版本和维护版本进行比较。我如何获取所有依赖项，即所需版本和最佳版本

I/P       20.0.0
O/p       20.0.1
          20.0.2
          20.0.3
          20.0.4
          20.0.5
          20.0.6

我得到的每个版本可能有也可能没有要求的最佳版本。我尝试使用查询进行了很多尝试，但没有获得如何在循环中调用的方法。请帮助我解决此问题。

Structure :                20.0.0 
                         /        \ 
        (reqver)   20.0.1          20.0.2 (optimumvers) 
                  /     \          /     \ 
            20.0.3     20.0.4    20.0.5   20.0.6
           (reqver)   (optver)  (req)      (opt)

提前致谢

彼得佩波

样本数据

-- Data preparation
CREATE TABLE REQUIRED_VERSION
  (
    MAJOR           NUMBER(3),
    MINOR           NUMBER(3),
    MAINTENANCE     NUMBER(3),
    REQUIREDVERSION VARCHAR2(11)
  );

CREATE TABLE OPTIMUM_VERSION
  (
    MAJOR          NUMBER(3),
    MINOR          NUMBER(3),
    MAINTENANCE    NUMBER(3),
    OPTIMUMVERSION VARCHAR2(11)
  );

-- Data
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','0','20.0.2');
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','2','20.0.6');
Insert into OPTIMUM_VERSION (MAJOR,MINOR,MAINTENANCE,OPTIMUMVERSION) values ('20','0','1','20.0.4');

Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','0','20.0.1');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','1','20.0.3');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','3',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','4',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','2','20.0.5');
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','5',null);
Insert into REQUIRED_VERSION (MAJOR,MINOR,MAINTENANCE,REQUIREDVERSION) values ('20','0','6',null);

询问

SELECT DISTINCT DEPENDENCY
FROM (
  SELECT VERSION,DEPENDENCY
  FROM (
    SELECT MAJOR||'.'||MINOR||'.'||MAINTENANCE AS VERSION, REQUIREDVERSION AS DEPENDENCY FROM REQUIRED_VERSION
    UNION
    SELECT MAJOR||'.'||MINOR||'.'||MAINTENANCE AS VERSION, OPTIMUMVERSION AS DEPENDENCY FROM OPTIMUM_VERSION
  )
  START WITH VERSION = '20.0.0' -- Put your version number here
  CONNECT BY PRIOR DEPENDENCY = VERSION AND DEPENDENCY IS NOT NULL
)
ORDER BY DEPENDENCY ASC;

解决方案由3个嵌套查询组成，从最深层解释开始。