本文介绍了如何在各部门中获得薪水最高的部门名称获取员工姓名。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 嗨朋友, 我有两张桌子1)EMP和2)DEPT EMP表: Empid Ename Esal deptId 1 raju 10000 10 2 rani 50000 20 3 pavan 7000 10 4 kumar 8000 30 DEPT表: Deptid Dname 10 IT 20非IT 30 HR 我想要输出:最高薪水部门明智的机智部门名称和emp详细信息 如下: Empid Ename Esal DName 1 raju 10000 IT 2 rani 50000非IT 4 kumar 8000 HR 建议我最好的查询:) 谢谢和问候 Syed Chand Basha 解决方案 假设你不喜欢关心关系,这样的事情应该有效: WITH RankedEmployees 作为 ( SELECT EmpId, EmpName, Esal, DeptId, ROW_NUMBER() OVER ( PARTITION BY DeptId ORDER BY Esal DESC ) As RN FROM Emp ) SELECT E.EmpId, E.EmpName, E.Esal, D .DName FROM Dept As D INNER JOIN RankedEmployees As E ON E.DeptID = D.DeptId 和 E.RN = 1 ; 如果您做想要显示关系,请将 ROW_NUMBER 替换为 RANK 。 ROW_NUMBER [ ^ ] RANK [ ^ ] Hi Friends,I am having two tables 1) EMP and 2) DEPTEMP table :Empid Ename Esal deptId1 raju 10000 102 rani 50000 203 pavan 7000 104 kumar 8000 30DEPT table:Deptid Dname 10 IT 20 Non IT 30 HRI want output: Highest Salary department wise along wit department name and emp detailsLike as follows:Empid Ename Esal DName 1 raju 10000 IT 2 rani 50000 Non IT 4 kumar 8000 HRPlease suggest me with best query :)Thanks & RegardsSyed Chand Basha 解决方案 Assuming you don't care about ties, something like this should work:WITH RankedEmployees As( SELECT EmpId, EmpName, Esal, DeptId, ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Esal DESC) As RN FROM Emp)SELECT E.EmpId, E.EmpName, E.Esal, D.DNameFROM Dept As D INNER JOIN RankedEmployees As E ON E.DeptID = D.DeptId And E.RN = 1;If you do want to show ties, replace ROW_NUMBER with RANK.ROW_NUMBER[^]RANK[^] 这篇关于如何在各部门中获得薪水最高的部门名称获取员工姓名。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-22 14:08