Oracle函数sys_connect_by_path与面试题

 

drop table tb;
create table tb(
  year number(4),
  quarter char(1),
  amount number(2,1)
);
delete from tb;
insert into tb values(2000,1,'1.1');
insert into tb values(2000,2,'1.2');
insert into tb values(2000,3,'1.3');
insert into tb values(2000,4,'1.4');
insert into tb values(2001,1,'2.1');
insert into tb values(2001,2,'2.2');
insert into tb values(2001,3,'2.3');
insert into tb values(2001,4,'2.4');
select * from tb;

 

但要求查询的结果是：

 

select year,ltrim(max(sys_connect_by_path(amount,',')),',')amount from(
  select amount,year,row_number() over(partition by year order by amount asc)
    rn from tb) start with rn = 1 connect by rn - 1 = prior rn and year = prior year group by year;