如何在各部门中获得薪水最高的部门名称获取员工姓名。 [英] How to get employee names along department names who having highest salary in respective departments.

问题描述

嗨朋友，



我有两张桌子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

解决方案

< blockquote>假设你不喜欢关心关系，这样的事情应该有效：

  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 
 < span class =code-keyword>和 E.RN =  1  
; 

如果您做想要显示关系，请将 ROW_NUMBER 替换为 RANK 。< br $>


ROW_NUMBER [ ^ ]

RANK [ ^ ]

Hi Friends,

I am having two tables 1) EMP and 2) DEPT

EMP table :
Empid Ename Esal deptId
1 raju 10000 10
2 rani 50000 20
3 pavan 7000 10
4 kumar 8000 30

DEPT table:
Deptid Dname
10 IT
20 Non IT
30 HR


I want output: Highest Salary department wise along wit department name and emp details
Like as follows:

Empid Ename Esal DName
1 raju 10000 IT
2 rani 50000 Non IT
4 kumar 8000 HR

Please suggest me with best query :)

Thanks & Regards
Syed 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.DName
FROM
    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[^]

这篇关于如何在各部门中获得薪水最高的部门名称获取员工姓名。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！