我有下表
id num sub_id
1 3 1
1 5 2
1 1 1
1 4 2
2 1 5
2 2 5
我想得到這個結果
id num sub_id number
1 3 1 1
1 5 2 2
1 1 1 1
1 4 2 2
2 1 5 1
2 2 5 1
我試圖這樣做， row_number() over (partition by id order by num,sub_id DESC)但結果明顯不同
uj5u.com熱心網友回復：
我不了解你的業務，因為你沒有解釋你的邏輯和資訊，但也許這個查詢對你有幫助？
結果和資訊：dbfiddle
with recursive
cte_r as (
select id,
num,
sub_id,
row_number() over () as rn
from test),
cte as (
select id,
num,
sub_id,
rn,
rn as grp
from cte_r
where rn = 1
union all
select cr.id,
cr.num,
cr.sub_id,
cr.rn,
case
when cr.id != c.id then 1
when cr.id = c.id and cr.sub_id = c.sub_id then c.grp
when cr.id = c.id and cr.sub_id > c.sub_id then c.grp 1
when cr.id = c.id and cr.sub_id < c.sub_id then 1
end
from cte c,
cte_r cr
where c.rn = cr.rn - 1)
select id,
num,
sub_id,
grp
from cte
order by id
uj5u.com熱心網友回復：
看起來您實際上想忽略該num列，然后使用DENSE_RANKon sub_id：
SELECT *, dense_rank() AS number OVER (PARTITION BY id ORDER BY sub_id) FROM …;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340202.html
標籤：PostgreSQL