暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 根据表中保存的记录生成动态记录

ASKTOM 2020-06-11
284

问题描述

动态记录应该基于id列生成。在表中,我们有id 1这样的记录,具有std值 (罗马格式) (i),(ii),(iii),然后生成的记录应该是id 1的std值 (罗马格式直到6) (i),(ii) 、 (iii) 、 (iv) 、 (v) 、 (vi)。但是,如果id 1也具有代码列值,则std列将为null,但将具有等级和代码值。下面是实际记录和预期记录的表格视图。

实际记录 (数据保存在表中):-
姓名id标准等级代码
ram-1 142174 (i) V
ram-1 142174 (ii) N
ram-1 142174 (iii) X
ram-1 142174 (iv) X
ram-1 142174 A A
ram-2 140965 (i) N
ram-2 140965 (iii) X
ram-3 140966 B B


预期记录:-
姓名id标准等级代码
ram-1 142174 (i) V
ram-1 142174 (ii) N
ram-1 142174 (iii) X
ram-1 142174 (iv) X
ram-1 142174 (v)
ram-1 142174 (vi)
ram-1 142174 A A
ram-2 140965 (i) N
ram-2 140965 (ii)
ram-2 140965 (iii) X
ram-2 140965 (iv)
ram-2 140965 (v)
ram-2 140965 (vi)
ram-3 140966 (i)
ram-3 140966 (ii)
ram-3 140966 (iii)
ram-3 140966 (iv)
ram-3 140966 (v)
ram-3 140966 (vi)
ram-3 140966 B B
这里是我的方法,使用与as子句
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
复制


示例表创建脚本:-
create table t11
    (
    name varchar2(20),
 id number,
    standard 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');
复制

专家解答

Partition outer连接到标准的6行表,然后在standadr为null的行中联合起来看起来像它会对我有用:

with rws as (
  select level n, '(' || trim ( to_char ( level, 'rn' ) ) || ')' rn 
  from   dual
  connect by level <= 6
)
  select t11.name,
         t11.id, 
         rws.rn, 
         t11.grade, 
         t11.code,
         rws.n 
  from   rws
  left join t11 
    partition by ( name, id )
  on     ( rn = standard )
  union  all
  select t11.*, null n
  from   t11
  where  standard is null
  order  by name, id, n;
  
NAME    ID       RN       GRADE    CODE     N        
ram-1       142174 (i)       V                     1 
ram-1       142174 (ii)      N                     2 
ram-1       142174 (iii)     X                     3 
ram-1       142174 (iv)      X                     4 
ram-1       142174 (v)                       5 
ram-1       142174 (vi)                      6 
ram-1       142174     A         A             
ram-2       140965 (i)       N                     1 
ram-2       140965 (ii)                      2 
ram-2       140965 (iii)     X                     3 
ram-2       140965 (iv)                      4 
ram-2       140965 (v)                       5 
ram-2       140965 (vi)                      6 
ram-3       140966 (i)                       1 
ram-3       140966 (ii)                      2 
ram-3       140966 (iii)                     3 
ram-3       140966 (iv)                      4 
ram-3       140966 (v)                       5 
ram-3       140966 (vi)                      6 
ram-3       140966     B         B             
复制

文章转载自ASKTOM,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论