在Oracle 10g中使用RANK或DENSE_RANK [英] using RANK or DENSE_RANK in Oracle 10g

问题描述

如何在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 < span class =code-keyword> 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 ; 

解决方案

< blockquote> 99,999.99'） TopProfit
FROM （
< span class =code-comment> / * ＃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 TopProfit
FROM (
        /* #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
     ) sqthree

WHERE ROWNUM = 1 and sqthree.Topprofit is not null;

解决方案

99,999.99') as TopProfit FROM ( /* #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 ) sqthree WHERE ROWNUM = 1 and sqthree.Topprofit is not null;

这篇关于在Oracle 10g中使用RANK或DENSE_RANK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！