字符串分组相加 row_number() 纵表转横表

--1.准备工作
create table TEST
(
  id VARCHAR2(10),
  mc VARCHAR2(50)
);

insert into test (ID, MC) values ('2', '11111');

insert into test (ID, MC) values ('2', '22222');

insert into test (ID, MC) values ('3', '22222');

insert into test (ID, MC) values ('3', '11111');

insert into test (ID, MC) values ('3', '33333');

insert into test (ID, MC) values ('1', '11111');

insert into test (ID, MC) values ('1', '22222');

 

--2.
--row_number() over ([partition by col1] order by col2) ) as 别名
--表示根据col1分组，在分组内部根据 col2排序
--而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）

select id, mc,
       row_number() over(partition by id order by mc) rn_by_id,
       row_number() over(order by id) + id rn
from test;

 

with tbl as
(select id, mc,
       row_number() over(partition by id order by mc) rn_by_id,
       row_number() over(order by id) + id rn
from test
)
select id, ltrim(max(sys_connect_by_path(mc,'/')),'/')
from tbl
start with rn_by_id = 1
connect by prior rn = rn -1
group by id
order by id
;

--3.如果id存的不是数字，而是随意的字符串怎么办
with tbl as
(select id, mc,
       row_number() over(partition by id order by mc) rn_by_id,
       row_number() over(order by id) + dense_rank() over(order by id) rn
from test
)
select id, ltrim(max(sys_connect_by_path(mc,'/')),'/')
from tbl
start with rn_by_id = 1
connect by prior rn = rn -1
group by id
order by id
;
--也就是将id替换为dense_rank() over(order by id)

 

 

--4.执行下面sql，助于理解。注意sql1与sql2结果的不同
--sql1
select id, mc,
       row_number() over(partition by id order by mc) rn_by_id,
       row_number() over(order by id) + dense_rank() over(order by id) rn
from test;
--sql2
select id, mc,
       row_number() over(partition by id order by id) rn_by_id,
       row_number() over(order by id) + dense_rank() over(order by id) rn
from test;
--sql3
select id, mc,
       row_number() over(order by id) rn,
       dense_rank() over(order by id) dr,
       rank() over(order by id) rk,
       row_number() over(partition by id order by id) rn_by_id,
       dense_rank() over(partition by id order by id) dr_by_id,
       rank() over(partition by id order by id) rk_by_id,
       row_number() over(partition by id order by mc) rn_by_mc,
       dense_rank() over(partition by id order by mc) dr_by_mc,
       rank() over(partition by id order by mc) rk_by_mc
from test;

 

--5.总结

row_number()涵数则是按照顺序依次使用，相当于我们普通查询里的rownum值；

rank()函数主要用于排序，并给出序号；

dense_rank()功能同rank()一样，区别在于，rank()对于排序并的数据给予相同序号，接下来的数据序号直接跳中跃，dense_rank()则不是，比如数据：
1，2，2，4，5，6      这是rank()的形式 
1，2，2，3，4，5      这是dense_rank()的形式
1，2，3，4，5，6      这是row_number()涵数形式

 

------------------------------------------------------------------------------------------

补充：还有 MIN (ROWID) OVER (PARTITION BY col)

select id, mc,
       MIN (ROWID) OVER (PARTITION BY id) id0,
       row_number() over(partition by id order by mc) id1,
       row_number() over(order by id) id2,
       dense_rank() over(order by id) id3
from test