将SQl查询转换为Oracle 10g查询 [英] Convert SQl query to Oracle 10g query

问题描述

将SQL查询转换为Oracle 10g查询



提问者：ocdc

如何将下面的查询转换为Oracle查询？ 的。 Oracle不使用TOP 1 WITH TIES

  SELECT   TOP   1   WITH  TIES 
 EmployeeID 
，EmployeeName 
，利润 AS    2ndTopProfit 
 
  FROM （ SELECT   TOP   2   WITH  TIES 
 
 S.EmpID  AS  EmployeeID 
，S.EName  AS  EmployeeName 
， '  $' + STR（（SUM（OI.Qty * I.Price） -  S.Salary）， 8 ， 2 ） AS 利润
  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 
 < span class =code-keyword> ORDER   BY （SUM（OI.Qty * I.Price） -  S.Salary） DESC ） AS  NEWTABLE   -   需要FROM子句中的表名 
 
  ORDER   BY   3   ASC ;   -   将第二大利润最高的人放在输出顶部 

解决方案

' + STR（（SUM（OI.Qty * I.Price） - S.Salary）， 8 ， 2 ） AS 利润
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
ORDER BY （SUM（OI.Qty * I.Price） - S.Salary） DESC ） AS NEWTABLE - 需要FROM子句中的表名

ORDER BY 3 ASC ; - 将第二大利润最高的人放在输出顶部

Oracle不使用TOP 1 WITH TIES，但你可以使用rank（）over（）函数代替它。

如下例：

  SELECT  * 
  FROM  
（ SELECT  empno，ename，orig_salary，
 DENSE_RANK（） OVER （ ORDER   BY  orig_salary  desc ）toprank 
  FROM  employee）
  WHERE  toprank< =  2  

RANK和DENSE_RANK处理关系



或者您可以使用ROWNUM获取第一条记录

如下例：

  SELECT  * 
  FROM  
（ SELECT  empno，ename，orig_salary，ROWNUM  as  toprank 
  FROM  employee）
  WHERE  toprank< =  2  

Convert SQL query to Oracle 10g query

Asked by: ocdc
How can I convert the query below to Oracle query? . Oracle doesn't use TOP 1 WITH TIES

SELECT      TOP 1 WITH TIES 
        EmployeeID
      , EmployeeName
      , Profit AS "2ndTopProfit"

FROM (SELECT TOP 2 WITH TIES

             S.EmpID AS EmployeeID
           , S.EName AS EmployeeName
           , '$' + STR((SUM(OI.Qty * I.Price) - S.Salary), 8, 2) AS Profit
       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
       ORDER BY (SUM(OI.Qty * I.Price) - S.Salary) DESC ) AS NEWTABLE   -- need table name in FROM clause

ORDER BY 3 ASC;  -- puts second most profitable person at top of output

解决方案

' + STR((SUM(OI.Qty * I.Price) - S.Salary), 8, 2) AS Profit 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 ORDER BY (SUM(OI.Qty * I.Price) - S.Salary) DESC ) AS NEWTABLE -- need table name in FROM clause ORDER BY 3 ASC; -- puts second most profitable person at top of output

hi , Oracle doesn't use "TOP 1 WITH TIES" , but you can use rank() over() function instead of it.
As following example :

SELECT *
FROM
(SELECT empno, ename, orig_salary,
DENSE_RANK() OVER(ORDER BY orig_salary desc) toprank
FROM employee)
WHERE toprank <= 2

RANK and DENSE_RANK handle ties

Or you can use the ROWNUM to get the first record
As following example :

SELECT *
FROM 
(SELECT empno, ename, orig_salary, ROWNUM as toprank
FROM employee)
WHERE toprank <= 2

这篇关于将SQl查询转换为Oracle 10g查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！