关于根据保存的数据生成动态记录的更新问题

Question

更新-实际-记录：

​

​

更新-预期-记录：

​

​

在表中，我们有像id 1这样的记录，它具有std值(罗马格式) (i)、(ii)、(iii)，那么生成的记录应该是对于具有std值的id 1(直到6或8) (i)、(ii)、(iii)、(iv)、(v)、(vi)。但是，如果id 1也有代码列值，那么std列将为null。

create table t11 
    ( 
    name varchar2(20), 
    id number, 
    std varchar2(20), 
    grade varchar2(20), 
    code varchar2(20) 
    );

insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(1,'RN')))||')','V',null);

insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(2,'RN')))||')','N',null);

insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(3,'RN')))||')','X',null);

insert into t11 values('ram-1', 142174, '('||trim(lower(to_char(4,'RN')))||')','X',null);

insert into t11 values('ram-1', 142174, null,'A','A');

insert into t11 values('ram-2', 140965, '('||trim(lower(to_char(1,'RN')))||')','N',null);

insert into t11 values('ram-2', 140965, '('||trim(lower(to_char(3,'RN')))||')','X',null);

insert into t11 values('ram-3', 140966,null,'B','B');

select * from t11;

我的一个好朋友试过了，现在我们需要帮助：

with cte (id, name, std, grade , code) as (
    select distinct id, name, 1, grade , code from t11
    union all
    select id, name, std + 1, grade , code from cte where std < 6
)
select id, name, '('||trim(lower(to_char(std,'RN')))||')' std, grade , code 
from cte
order by id, std

Answer 1

使用已分区的外部联接进行数据加密(创建行)。分区的外部联接是对值的每个实例重复的联接-在本例中，您希望对每个名称应用7行罗马数字行的外部联接。

此查询还需要分析函数来填充缺少的ID。还有-你的样本数据是不是有点错了？ram-3的值应该是140966而不是140965吗？

select
    t11.name,
    coalesce(
        t11.id,
        lag(t11.id ignore nulls) over (partition by name order by roman_numerals.std nulls first),
        lead(t11.id ignore nulls) over (partition by name order by roman_numerals.std nulls first)
    ) test,
    roman_numerals.std,
    t11.grade,
    t11.code
from
(
    select ''      std from dual union all
    select '(i)'   std from dual union all
    select '(ii)'  std from dual union all
    select '(iii)' std from dual union all
    select '(iv)'  std from dual union all
    select '(v)'   std from dual union all
    select '(vi)'  std from dual
) roman_numerals
left outer join t11 partition by (t11.name)
    on nvl(t11.std, 'no value') = nvl(roman_numerals.std, 'no value')
order by name, roman_numerals.std nulls last;