原始数据
type 分 1,2,3,4,5
select name,yield ,type
--type将数据分成不同组,为每组生成编号
,row_number() over(partition by type order by yield desc) rn
--,dense_rank() over(order by yield desc) rn
from AAA
根据分组生成每组对应的编号
最终sql
select max(case when type = 1 then name end) as lv1 , max(decode(type,1,yield)) Ayield, max(case when type = 2 then name end) as lv2 , max(decode(type,2,yield)) Byield, max(case when type = 3 then name end) as lv3 , max(decode(type,3,yield)) Cyield, max(case when type = 4 then name end) as lv4 , max(decode(type,4,yield)) Dyield, max(case when type = 5 then name end) as lv5 , max(decode(type,5,yield)) Eyield from ( select name,yield ,type --type将数据分成不同组,为每组生成编号 ,row_number() over(partition by type order by yield desc) rn --,dense_rank() over(order by yield desc) rn from AAA ) group by rn order by rn
如果没有分类,可以加一列分组
select max(case when lev = 1 then name end) as lv1,
max(case when lev = 2 then name end) as lv2 ,
max(case when lev = 3 then name end) as lv3
from
(
select name ,yield ,
--对数据分组
case when rn <=16 then 1 when rn <= 32 then 2 else 3 end lev
--不同分组编号
,row_number() over(partition by (case when rn <= 16 then 1 when rn <= 32 then 2 else 3 end ) order by yield desc) rn
from
(
select name,yield
--,row_number() over(partition by name order by yield desc) rn
,dense_rank() over(order by yield desc) rn
from AAA
)
)
group by rn
--order by rn