姓名 TOP1类别 TOP1花费 TOP2类别 TOP2花费 TOP3类别 TOP3花费
李 旅游 500 吃饭 100 看电影 30
王 买衣服 700 吃饭 500 看电影 100
这题目其实挺简单的,横列转换。效率最高的答案,看最后,。。。。/// 如果有更好的, 欢迎讨论
---------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT NAME,
MAX(DECODE(SEQ, 1, TYPE, NULL)) HIGHEST_TYPE,
MAX(DECODE(SEQ, 1, COST, NULL)) HIGHEST_COST,
MAX(DECODE(SEQ, 2, TYPE, NULL)) SECOND_TYPE,
MAX(DECODE(SEQ, 2, COST, NULL)) SECOND_COST,
MAX(DECODE(SEQ, 3, TYPE, NULL)) THIRD_TYPE,
MAX(DECODE(SEQ, 3, COST, NULL)) THIRD_COST
FROM (SELECT NAME,
COST,
TYPE,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY COST DESC NULLS LAST) SEQ
FROM TEST)
GROUP BY NAME;
本文原创发布php中文网,转载请注明出处,感谢您的尊重!