根据表[oracle sql query]中保存的记录生成动态记录有关要求，请参阅图片链接

qc6wkl3g 于 2021-07-29 发布在 Java

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

实际记录：

预期记录：

create table和insert语句here:-

create table t11
    (
    id number,
    name varchar2(20),
    standard varchar2(20),
    sdt date
    );

insert into t11 values(1,'Ramakrushna',to_char(1,'RN'), sysdate);

insert into t11 values(1,'Ramakrushna',to_char(2,'RN'), sysdate);   

insert into t11 values(2,'Ramakrushna',to_char(1,'RN'),sysdate);

insert into t11 values(2,'Ramakrushna',to_char(5,'RN'),sysdate);

insert into t11 values(3,'Ramakrushna',to_char(1,'RN'),sysdate);

动态记录基于id，它应该与id一起生成8条记录。表中保存的记录如下，对于id 1记录可以是i和iii标准（罗马值），但id 1的预期记录将是，i、ii、iii、iv、v、vi、vii、viii，其他id也一样，基于表中保存的记录。
这是我的方法，使用with as子句。它几乎不需要修改。

with t01 as (
    select rownum rn,id,name,standard,sdt from t11
    ),
    t02 as (
    select rownum rn ,id,name,to_char(to_char(l,'RN')) as standard,sdt,l
       from t01
          left outer join (select level l from dual connect by level <= ((select max(rn) from t01) + 6)) lev on 1 = 1 -- passed 2 as argument
    ),
    t03 as (
    select  t02.id,t02.name,t02.standard,t02.sdt,
    row_number() over(partition by standard order by 1) rnk
    from t02 where not exists (select 'Y' from t01 where t01.standard = t02.standard)
    )
    select id,name,standard,sdt from t03 where rnk = 1
    union
    select id,name,standard,sdt from t1;

sql select oracle recursive-query

来源：https://stackoverflow.com/questions/62312106/generate-dynamic-record-based-on-the-record-saved-in-the-tableoracle-sql-query

1条答案

按热度按时间

v7pvogib1#

如果 name 以及 sdt 对于每个 id ，如示例数据中所示，那么一种简单的方法就是只选择不同的元组，并使用递归查询生成所有数字：

with cte (id, name, standard, sdt) as (
    select distinct id, name, 1, sdt from t11
    union all
    select id, name, standard + 1, sdt from cte where standard < 8
)
select id, name, to_char(standard, 'RN') standard, sdt 
from cte
order by id, standard

我喜欢使用常用的表表达式而不是 connect by ，因为它们是标准的sql，并且在oraclce中支持了相当长的一段时间，但是逻辑与 connect by :

select distinct id, name, to_char(level, 'RN') standard, sdt 
from t11
connect by level <= 8
order by id, standard

db小提琴演示：

ID | NAME        | STANDARD        | SDT      
-: | :---------- | :-------------- | :--------
 1 | Ramakrushna |               I | 10-JUN-20
 1 | Ramakrushna |               V | 10-JUN-20
 1 | Ramakrushna |              II | 10-JUN-20
 1 | Ramakrushna |              IV | 10-JUN-20
 1 | Ramakrushna |              VI | 10-JUN-20
 1 | Ramakrushna |             III | 10-JUN-20
 1 | Ramakrushna |             VII | 10-JUN-20
 1 | Ramakrushna |            VIII | 10-JUN-20
 2 | Ramakrushna |               I | 10-JUN-20
 2 | Ramakrushna |               V | 10-JUN-20
 2 | Ramakrushna |              II | 10-JUN-20
 2 | Ramakrushna |              IV | 10-JUN-20
 2 | Ramakrushna |              VI | 10-JUN-20
 2 | Ramakrushna |             III | 10-JUN-20
 2 | Ramakrushna |             VII | 10-JUN-20
 2 | Ramakrushna |            VIII | 10-JUN-20
 3 | Ramakrushna |               I | 10-JUN-20
 3 | Ramakrushna |               V | 10-JUN-20
 3 | Ramakrushna |              II | 10-JUN-20
 3 | Ramakrushna |              IV | 10-JUN-20
 3 | Ramakrushna |              VI | 10-JUN-20
 3 | Ramakrushna |             III | 10-JUN-20
 3 | Ramakrushna |             VII | 10-JUN-20
 3 | Ramakrushna |            VIII | 10-JUN-20

赞(0）回复(0）举报 2021-07-29

我来回答

根据表[oracle sql query]中保存的记录生成动态记录有关要求，请参阅图片链接

1条答案

相关问题

热门标签

最新问答