表scoretable：
| ID | 姓名 | 分數 |
|---|---|---|
| 101 | 大號 | 10 |
| 101 | 米 | 9 |
| 101 | ? | 10 |
| 102 | ○ | 10 |
| 102 | X | 10 |
| 103 | 磷 | 8 |
| 104 | 問 | 9 |
| 104 | R | 8 |
輸出：
| ID | 第一的 | 第二 | 第三 |
|---|---|---|---|
| 101 | L,N | 米 | |
| 102 | 牛 | ||
| 103 | 磷 | ||
| 104 | 問 | R |
下面是我的解決方案，我在其中獲得相同 ID 的多行。我的解決方案：
with
t1 as(select ID, name, Score, dense_rank() over(partition by ID order by Score desc) as rnk from scoretable),
t2 as(select t1.id, (case when t1.rnk=1 then string_agg(t1.name,' ') end) as first from t1 group by t1.id,t1.rnk),
t3 as(select t1.id, (case when t1.rnk=2 then string_agg(t1.name,' ') end) as second from t1 group by t1.id,t1.rnk),
t4 as(select t1.id, (case when t1.rnk=3 then string_agg(t1.name,' ') end) as third from t1 group by t1.id,t1.rnk)
select distinct t1.id,t2.first,t3.second,t4.third
from t1,t2,t3,t4
where t1.id=t2.id and t2.id=t3.id and t3.id=t4.id
group by t1.id,t2.first,t3.second,t4.third
order by t1.id;
uj5u.com熱心網友回復：
排名計算沒問題。但是使用條件聚合而不是三個連接來構建結果：
with cte as (
select id
, name
, dense_rank() over (partition by id order by score desc) as dr
from t
)
select id
, group_concat(case when dr = 1 then name end separator ', ') as `first`
, group_concat(case when dr = 2 then name end separator ', ') as `second`
, group_concat(case when dr = 3 then name end separator ', ') as `third`
from cte
where dr <= 3
group by id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/445199.html
上一篇：如何按原樣加入sql子查詢？