基于Mgr_ID的每组的第2个最高记录，包括经理组（Null组） [英] 2nd Highest record from each group based on Mgr_ID including Manager group (Null group)

问题描述

TableName：EMP_MGR

TableName: EMP_MGR

Emp_ID                 Emp_Name              Salary                      Mgr_ID
1                      Aman                  45000                        NULL
2                      Deepak                35000                           1
3                      Pankaj                32000                           1
4                      Sapna                 25000                           1
5                      Rajan                 50000                        NULL
6                      Nupur                 18000                           5
7                      Anamika               18000                           5
8                      Preet                 22000                        NULL
9                      Shalu                 27000                        NULL
10                    Jyoti                  12000                           9
11                    Omesh                  25000                           9
12                    Rakesh                 21000                           9

SELECT MAX(salary),E1.mgr_id FROM EMP_MGR E1,
(SELECT MAX(salary) AS sal,mgr_id FROM EMP_MGR GROUP BY mgr_id ) E2 
WHERE E1.salary<e2.sal and="" e1.mgr_id="E2.mgr_id"

group =by = mode =hold/>

我想从每个组中找到第二高的记录，包括经理（空组）

预期结果：

group="" by="" mode="hold" />
I want to find 2nd Highest record from each group including Managers(NULL group)
Expected Result:

Mgr_ID           Salary
NULL              45000
1                     32000
5                     18000
9                     15000

推荐答案

Not确定下面对你没问题。只需尝试一次......

Not sure below would be fine for you or not. Just try once...

;WITH CTE
(
    SELECT salary,mgr_id FROM EMP_MGR GROUP BY mgr_id
)
SELECT MAX(SALARY), mgr_id FROM CTE
WHERE SALARY <> (SELECT MAX(SALARY) FROM CTE)

select * from（select *，rank（）over（mgd_id的mgr_id顺序除以）作为来自emp_mgr的rno）为T，其中rno = 2

select * from(select *, rank() over(partition by mgr_id order by mgrid desc) as rno from emp_mgr) as T where rno = 2

这篇关于基于Mgr_ID的每组的第2个最高记录，包括经理组（Null组）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！