下面是條件和效果。
uj5u.com熱心網友回復：
with tab1 as(
select '螺釘' name, 10 amount,'介入手術室1' dept from dual union all
select '螺釘' name, 20 amount,'介入手術室2' dept from dual union all
select '螺釘' name, 15 amount,'介入手術室3' dept from dual union all
select '接骨板' name, 100 amount,'介入手術室1' dept from dual union all
select '接骨板' name, 200 amount,'介入手術室2' dept from dual union all
select '接骨板' name, 300 amount,'介入手術室3' dept from dual union all
select '電刀' name, 1 amount,'介入手術室1' dept from dual union all
select '電刀' name, 2 amount,'介入手術室2' dept from dual union all
select '電刀' name, 3 amount,'介入手術室3' dept from dual union all
select '電刀' name, 4 amount,'介入手術室4' dept from dual
)
select name,sumcount,listagg (dept, ',') WITHIN GROUP (ORDER BY dept) a
from (
SELECT
name,
dept,
sum(amount) OVER(partition by name) sumcount ,
ROW_NUMBER() OVER(partition by name order by amount) rn
FROM
tab1)
where rn<4
group by name,sumcount order by sumcount desc;
uj5u.com熱心網友回復：
大佬牛逼，感謝
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/253624.html
標籤：基礎和管理