Oracle不定行输出三列，新手求教

[qq_38932370]

如图，大概有1000多car_id，对应多个car_num、car_diqu，每一个car_diqu都不同
------------------------------------
car_id car_diqu car_num
-------------------------------------
1 01 1000
1 02 1999
1 11 299
1 21 23
2 132 233
2 123 2331
3 112 12212
3 1132 111
3 111 1342
.......
输出成下列格式，输出成三列，根据每一个car_id的car_num的大小排序，选取前三个输出成car_diqu1 car_diqu2 car_diqu3
若是不够三个可以为空或者0

----------------------------------------------------------
car_id car_diqu1 car_diqu2 car_diqu3
----------------------------------------------------------
1 02 01 11
2 123 132
3 112 111 1132
.........

希望各位大神解答，给出语句参考

[yaiger]

with s as
( select *
from (select car_id,
car_diqu,
row_number() over(partition by car_id order by car_num) rn
from table)
where rn <= 3)
select car_id,
max(decode(s.rn, 1, car_diqu, null)) as car_diqu1,
max(decode(s.rn, 2, car_diqu, null)) as car_diqu2,
max(decode(s.rn, 3, car_diqu, null)) as car_diqu3
from s
group by car_id
order by car_id

[yaiger]

少写了个DESC

with s as
( select *
from (select car_id,
car_diqu,
row_number() over(partition by car_id order by car_num desc) rn
from table)
where rn <= 3)
select car_id,
max(decode(s.rn, 1, car_diqu, null)) as car_diqu1,
max(decode(s.rn, 2, car_diqu, null)) as car_diqu2,
max(decode(s.rn, 3, car_diqu, null)) as car_diqu3
from s
group by car_id
order by car_id

[qq_38932370]

谢谢大哥了，新手刚接触sql有点头疼，我本来想用privot的，发现取值不好取

[yaiger]

pivot只能针对固定值，不适用你的情况