在Oracle 10g中使用RANK或DENSE_RANK

本文介绍了在Oracle 10g中使用RANK或DENSE_RANK的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 如何在Oracle 10g中使用 Rank 或 Dense_rank 重新编写下面的查询？ SELECT sqthree.employeeid， sqthree.employeename， to_char（sqthree.Topprofit，' $ 99,999.99'） as TopProfit FROM （ / * ＃3 - 选择第一和第二position * / SELECT sqtwo.employeeid，sqtwo.employeename，sqtwo.TopProfit FROM （ / * ＃2 - 订购最里面的* / SELECT innermost.employeeid，innermost.employeename，innermost.TopProfit FROM （ / * ＃1 - 最里面的子查询* / SELECT s.empid employeeid， s.ename employeename， / * 此处使用NVL * / NVL（SUM（i.price * oi.qty）， 0 ） - NVL（s.salary， 0 ）TopProfit FROM salespersons s LEFT JOIN 命令o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid， s.ename，s.salary ）最里面 ORDER BY TopProfit DESC ）sqtwo WHERE ROWNUM = 1 ORDER BY TopProfit DESC ）sqthree WHERE ROWNUM = 1 和 sqthree.Topprofit 不 null ; 解决方案 99,999.99'） TopProfit FROM （ / * ＃3 - 选择第一和第二位置* / SELECT sqtwo.employeeid，sqtwo.employeename，sqtwo.TopProfit FROM （ / * ＃2 - 订购最里面的* / SELECT innermost.employeeid，innermost.employeename，innermost.TopProfit FROM （ / * ＃1 - 最里面的子查询* / SELECT s.empid employeeid， s.ename employeename， / * 此处使用NVL * / NVL（SUM（i.price * oi.qty）， 0 ） - NVL（s.salary， 0 ）TopProfit FROM salespersons s LEFT JOIN 命令o ON s.empid = o .empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid，s.ename，s.salary ）innermost ORDER BY TopProfit DESC ）sqtwo WHERE ROWNUM = 1 ORDER BY TopProfit DESC ）sqthree WHERE ROWNUM = 1 和 sqthree.Topprofit 不 null ; How can I re-write the query below using Rank or Dense_rank in Oracle 10g?SELECT sqthree.employeeid, sqthree.employeename, to_char(sqthree.Topprofit,'$99,999.99') as TopProfitFROM ( /* #3 - select First and Second position */ SELECT sqtwo.employeeid, sqtwo.employeename, sqtwo.TopProfit FROM ( /* #2 - order the innermost */ SELECT innermost.employeeid, innermost.employeename, innermost.TopProfit FROM ( /* #1 - innermost subquery */ SELECT s.empid employeeid, s.ename employeename, /* used NVL here */ NVL(SUM(i.price * oi.qty),0) - NVL(s.salary,0) TopProfit FROM salespersons s LEFT JOIN orders o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid, s.ename, s.salary ) innermost ORDER BY TopProfit DESC ) sqtwo WHERE ROWNUM = 1 ORDER BY TopProfit DESC ) sqthreeWHERE ROWNUM = 1 and sqthree.Topprofit is not null; 解决方案 99,999.99') as TopProfitFROM ( /* #3 - select First and Second position */ SELECT sqtwo.employeeid, sqtwo.employeename, sqtwo.TopProfit FROM ( /* #2 - order the innermost */ SELECT innermost.employeeid, innermost.employeename, innermost.TopProfit FROM ( /* #1 - innermost subquery */ SELECT s.empid employeeid, s.ename employeename, /* used NVL here */ NVL(SUM(i.price * oi.qty),0) - NVL(s.salary,0) TopProfit FROM salespersons s LEFT JOIN orders o ON s.empid = o.empid LEFT JOIN orderitems oi ON o.orderid = oi.orderid LEFT JOIN inventory i ON oi.partid = i.partid GROUP BY s.empid, s.ename, s.salary ) innermost ORDER BY TopProfit DESC ) sqtwo WHERE ROWNUM = 1 ORDER BY TopProfit DESC ) sqthreeWHERE ROWNUM = 1 and sqthree.Topprofit is not null; 这篇关于在Oracle 10g中使用RANK或DENSE_RANK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！