问题描述
动态记录应该基于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子句
示例表创建脚本:-
实际记录 (数据保存在表中):-
姓名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) V1 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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
679次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
634次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
542次阅读
2025-04-20 10:07:02
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
491次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
488次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
472次阅读
2025-04-22 00:13:51
一页概览:Oracle GoldenGate
甲骨文云技术
467次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
416次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
372次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
352次阅读
2025-05-05 19:28:36