英文:
How do I find the staff with the highest salary in every occupation from the table below (Oracle SQL)
问题
你想要找到每个职业中薪水最高的员工，可以使用以下Oracle SQL查询：
SELECT Occupation, MAX(Salary) AS Max_SalaryFROM STAFFGROUP BY Occupation;
这个查询将按职业分组，找到每个职业中的最高薪水。
英文:
How do I find the staff with the highest salary in every occupation from the table below (Oracle SQL). This is my table below. Any help will be appreciated.
CREATE TABLE STAFF (Staff_ID NUMBER(5),First_Name VARCHAR2(25),Last_Name VARCHAR2(25),Date_Of_Birth DATE,Gender VARCHAR2(25),Email VARCHAR2(25),Telephone NUMBER(15),Department VARCHAR2(25),Staff_Type VARCHAR2(25),Visa_Restrictions VARCHAR2(40),Occupation VARCHAR2(25),Hire_Date DATE,Salary NUMBER (10),CONSTRAINT PK_STAFF PRIMARY KEY (Staff_ID));
答案1
得分: 1
以下是翻译好的部分：
"你发布的代码完成了任务并返回了正确的结果。不过，你两次访问了 staff 表，所以从性能的角度来看，我认为可能可以改进。这里有一个选项：使用 rank 分析函数，在按工资降序排列的情况下为每个职业排名，然后检索排名最高的行。由于此查询只访问表一次，如果你需要处理大量行，它应该性能更好。
对于示例数据：
SQL> select * from staff order by occupation, salary desc;ENAME OCCUPATIO SALARY---------- --------- ----------FORD ANALYST 3225SCOTT ANALYST 3225MILLER CLERK 1365ADAMS CLERK 1182.5JAMES CLERK 1045SMITH CLERK 860JONES MANAGER 3198.13BLAKE MANAGER 3135CLARK MANAGER 2572.5KING PRESIDENT 5250ALLEN SALESMAN 1760TURNER SALESMAN 1650MARTIN SALESMAN 1375WARD SALESMAN 137514 rows selected.
查询：
SQL> with temp as2 (select s.*,3 rank() over (partition by s.occupation order by s.salary desc) rnk4 from staff s5 )6 select t.ename, t.occupation, t.salary7 from temp t8 where t.rnk = 19 order by t.occupation;ENAME OCCUPATIO SALARY---------- --------- ----------SCOTT ANALYST 3225FORD ANALYST 3225MILLER CLERK 1365JONES MANAGER 3198.13KING PRESIDENT 5250ALLEN SALESMAN 17606 rows selected.SQL>";
英文:
Code you posted does the job and returns correct result. Though, you're accessing the staff table twice, so - performance wise - I guess it could be improved. Here's one option: use rank analytic function which ranks rows for each occupation by salary in descending order, and then retrieve rows that ranked as the highest. As this query accesses the table only once, it should perform better if you have to deal with a lot of rows.
For sample data:
SQL> select * from staff order by occupation, salary desc;ENAME OCCUPATIO SALARY---------- --------- ----------FORD ANALYST 3225SCOTT ANALYST 3225MILLER CLERK 1365ADAMS CLERK 1182.5JAMES CLERK 1045SMITH CLERK 860JONES MANAGER 3198.13BLAKE MANAGER 3135CLARK MANAGER 2572.5KING PRESIDENT 5250ALLEN SALESMAN 1760TURNER SALESMAN 1650MARTIN SALESMAN 1375WARD SALESMAN 137514 rows selected.
Query:
SQL> with temp as2 (select s.*,3 rank() over (partition by s.occupation order by s.salary desc) rnk4 from staff s5 )6 select t.ename, t.occupation, t.salary7 from temp t8 where t.rnk = 19 order by t.occupation;ENAME OCCUPATIO SALARY---------- --------- ----------SCOTT ANALYST 3225FORD ANALYST 3225MILLER CLERK 1365JONES MANAGER 3198.13KING PRESIDENT 5250ALLEN SALESMAN 17606 rows selected.SQL>
答案2
得分: 0
Sure, here's the translated SQL query:
选择 s1.职业, s1.名字, s1.姓氏, s1.工资从 员工 s1其中 s1.工资 = (选择 最大值(s2.工资)从 员工 s2其中 s2.职业 = s1.职业)按 s1.职业 升序排序;
英文:
SELECT s1.Occupation, s1.First_Name, s1.Last_Name, s1.SalaryFROM STAFF s1WHERE s1.Salary = (SELECT MAX(s2.Salary)FROM STAFF s2WHERE s2.Occupation = s1.Occupation)ORDER BY s1.Occupation ASC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论