oracle向上移一行,oracle 数据库里面的数据实现上移下移功能

2018-05-17 回答

_concat功能来行转列 create table idtable (id number,name varchar2(30)); insert into idtable values(10,'ab'); insert into idtable values(10,'bc'); insert into idtable values(10,'cd'); insert into idtable values(20,'hi'); insert into idtable values(20,'ij'); insert into idtable values(20,'mn'); insert into idtable values(30,'za'); insert into idtable values(30,'zb'); insert into idtable values(30,'zc'); select * from idtable --按同一个id,把行内容转换为列内容 select id,_concat(name) name from idtable group by id; --每行id都把相同id的行内容转换为列,然后根据id的排序进行内容叠加. select id,_concat(name) over (order by id) name from idtable --按照id,name进行排名,每一行内容都汇总前一行的内容(内容叠加) select id,_concat(name) over (order by id,name) name from idtable; --每行id都把条件相同id的行内容转换为列内容 select id,_concat(name) over (partition by id) name from idtable; 利用正则表达式实现字段拆分(列转行) 一、数据准备 sql> drop table temp; sql> create table temp (col1 varchar2(3), col2 varchar2(200)); sql> insert into temp values('a','a,aa,aaa'); sql> insert into temp values('b','b,bb,bbb'); sql> select * from temp; col1 col2 ------- ----------------- a a,aa,aaa b b,bb,bbb 二、实现目的 col1 col2 ------- ----------------- a a a aa a aaa b b b bb b bbb 三、实现方法 select col1, regexp_substr(col2, '[^,]+', 1, n) col2 from temp, (select rownum n from dual connect by rownum < 100) where n <= length(regexp_replace(col2, '[^,]', null)) + 1 order by 1, 2