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

本文介绍了将SQl查询转换为Oracle 10g查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 将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 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 queryAsked by: ocdcHow can I convert the query below to Oracle query? . Oracle doesn't use TOP 1 WITH TIESSELECT 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 clauseORDER 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 clauseORDER BY 3 ASC; -- puts second most profitable person at top of outputhi , 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) toprankFROM employee)WHERE toprank <= 2RANK and DENSE_RANK handle tiesOr you can use the ROWNUM to get the first recordAs following example :SELECT *FROM(SELECT empno, ename, orig_salary, ROWNUM as toprankFROM employee)WHERE toprank <= 2 这篇关于将SQl查询转换为Oracle 10g查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！