WITH T AS
(SELECT '张三' a, 89 b, 78 c,90 d FROM DUAL
UNION ALL
SELECT '李四' a, 77 b, 69 c, 80 d FROM DUAL
UNION ALL
SELECT '王五' a, 74 b, 80 c,74 d FROM DUAL
UNION ALL
SELECT '赵六' a, 89 b, 87 c,92 d FROM DUAL)
select nn.a,
sum(decode(rnn, '1', score, 0)),
sum(decode(rnn, '2', score, 0)),
sum(decode(rnn, '3', score, 0))
from (select t1.a,
t2.score,
row_number() over(PARTITION BY t2.a ORDER BY t2.score desc) rnn
from t t1,
(select a, b score from t
union all
select a, c score from t
union all
select a, d score from t) t2
where t1.a = t2.a) nn
group by nn.a
order by nn.a
(SELECT '张三' a, 89 b, 78 c,90 d FROM DUAL
UNION ALL
SELECT '李四' a, 77 b, 69 c, 80 d FROM DUAL
UNION ALL
SELECT '王五' a, 74 b, 80 c,74 d FROM DUAL
UNION ALL
SELECT '赵六' a, 89 b, 87 c,92 d FROM DUAL)
select nn.a,
sum(decode(rnn, '1', score, 0)),
sum(decode(rnn, '2', score, 0)),
sum(decode(rnn, '3', score, 0))
from (select t1.a,
t2.score,
row_number() over(PARTITION BY t2.a ORDER BY t2.score desc) rnn
from t t1,
(select a, b score from t
union all
select a, c score from t
union all
select a, d score from t) t2
where t1.a = t2.a) nn
group by nn.a
order by nn.a
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货