通过添加UPDATE语句来修改查询 [英] modify query by adding UPDATE statement

问题描述

以下查询有效，并为我提供了TopProfitted销售人员。



如何修改下面的查询以包含 UPDATE 语句以增加12％的薪水？



---找到最顶级的销售人员并增加其工资 - 销售人员12％



< pre lang =SQL> SELECT *
FROM （ SELECT *
FROM （ SELECT *
< span class =code-keyword> FROM （ SELECT s.empid employeeid，
s.ename employeename，
SUM（ i.price * oi.qty） - s.salary 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 ）
WHERE ROWNUM = 2
ORDER BY Topprofit ASC ）
WHERE ROWNUM = 1 和 Topprofit 不 null ;

解决方案

类似这个。

警告：我的语法可能不完全正确

更新salespersons set salary = salary * 1.12其中salespersons.empid = 
（SELECT employeeid 
 FROM（SELECT * 
 FROM（SELECT * 
 FROM（SELECT s.empid employeeid，
 s.ename employeename，
 SUM（i.price * oi。 qty） -  s.salary Topprofit 
 FROM salespersons s 
 LEFT JOIN order o ON s.empid = o.empid 
 LEFT JOIN orderitems oi ON o.orderid = oi.orderid 
 LEFT JOIN库存i ON oi.partid = i.partid 
 GROUP BY s.empid，s.ename，s.salary）
 ORDER BY Topprofit DESC）
 WHERE ROWNUM = 2 
 ORDER BY Topprofit ASC）
 WHERE ROWNUM = 1且Topprofit不为空）; 

The query below works and gives me the most TopProfitted salesperson.

How can I modify the query below to include UPDATE statement to increase the salary of 12% ?

---Find the most TopProfited salesperson and increase the salary of that --salesperson 12%

SELECT *
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT s.empid employeeid,
                               s.ename employeename,
                               SUM(i.price * oi.qty) - s.salary 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)
                ORDER BY Topprofit DESC)
         WHERE ROWNUM = 2
        ORDER BY Topprofit ASC)
 WHERE ROWNUM = 1 and Topprofit is not null;

解决方案

Something like this.
Warning: My syntax may not be entirely correct

Update salespersons set salary = salary * 1.12 where salespersons.empid =
(SELECT employeeid
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT s.empid employeeid,
                               s.ename employeename,
                               SUM(i.price * oi.qty) - s.salary 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)
                ORDER BY Topprofit DESC)
         WHERE ROWNUM = 2
        ORDER BY Topprofit ASC)
 WHERE ROWNUM = 1 and Topprofit is not null);

这篇关于通过添加UPDATE语句来修改查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！