如何在 MySQL 中的 select 语句中限制结果组

发布于 2025-01-17 04:27:08 字数 1175 浏览 0 评论 0原文

员工表
数据库中的表1

问题是：列出属于同一部门且工资最高的员工和经理它们之间的区别

我可以得到以下结果：
它按同一部门的经理和员工的工资差异降序显示按部门排序的列表。

的查询：

select dno dn,emp_n,abs(mgr_sal - e_sal) diff, emp_n e_n, mgr_n m_n 
from (select m.deptno dno,m.sal mgr_sal,e.sal e_sal,e.ename emp_n,m.ename mgr_n
      from emp e join emp m on e.mgr = m.empno 
      where e.deptno = m.deptno ) dt order by dno,diff desc;

我根据这个问题，我只需要最大差异分组部门明智的结果。从上面的结果来看，我只需要属于每个部门的同一部门的员工和经理之间最大工资差异的行。即，

dn	diff	emp_name	mgr_name
10	2550	CLARK	KING
20	2200	SMITH	FORD
30	1900	JAMES	BLAKE

问题是从派生表中获取员工和经理的姓名。组函数始终返回第一行值，这与部门号恰好一致。第10排和第20排的员工和经理是<克拉克·金>。和<史密斯，福特>并在部门号。 30 其（这就是我发现结果错误的原因！）。如何让具有最大薪资差异的员工和他们的经理属于同一部门？

原文

emp table
Table 1 in the database

Question is : List the employees and manager belonging to the same department and has the maximum salary difference between them

I could get till the following results:
Its shows department wise sorted list in descending order of difference in salary of manager and employee of the same department.

My Query:

select dno dn,emp_n,abs(mgr_sal - e_sal) diff, emp_n e_n, mgr_n m_n 
from (select m.deptno dno,m.sal mgr_sal,e.sal e_sal,e.ename emp_n,m.ename mgr_n
      from emp e join emp m on e.mgr = m.empno 
      where e.deptno = m.deptno ) dt order by dno,diff desc;

As per the question I need only maximum difference grouped department wise results. From the above results I need only rows with maximum salary difference between an employee and manager both belonging to the same department from each department.
i.e.,

dn	diff	emp_name	mgr_name
10	2550	CLARK	KING
20	2200	SMITH	FORD
30	1900	JAMES	BLAKE

The problem is getting the employee and managers' name from the derived table. The group function always returns the first row values which coincidentally for dept no. 10 and 20's row employee and manager are <Clark, King> and <Smith, Ford> and in dept no. 30 its <Allen, Blake>(that's how I found out the results are wrong!).
How do I get the employee and their manager having maximum salary difference belonging to the same department?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不再让梦枯萎 2025-01-24 04:27:08

像您一样进行自连接以检索员工和经理姓名后，您可以通过在 deptno 上分区并按 diff< 排序，使用诸如 ROW_NUMBER 的排名函数/code> 后代。

WITH emps_and_mgrs AS (
    SELECT 
        employees.deptno,
        managers.sal - employees.sal    AS diff,
        employees.ename                 AS emp_name,
        managers.ename                  AS mgr_name,
        ROW_NUMBER() OVER(
            PARTITION BY deptno 
            ORDER BY managers.sal - employees.sal DESC)         AS rank
    FROM 
        tab employees
    INNER JOIN 
        tab managers
    ON 
        employees.mgr = managers.empno AND employees.deptno = managers.deptno
)
SELECT 
    deptno,
    diff,
    emp_name,
    mgr_name
FROM 
    emps_and_mgrs
WHERE 
    rank = 1

After doing a self join to retrieve the employee and manager name like you did, you can use a ranking function like ROW_NUMBER by partitioning on deptno and ordering by diff descendent.

WITH emps_and_mgrs AS (
    SELECT 
        employees.deptno,
        managers.sal - employees.sal    AS diff,
        employees.ename                 AS emp_name,
        managers.ename                  AS mgr_name,
        ROW_NUMBER() OVER(
            PARTITION BY deptno 
            ORDER BY managers.sal - employees.sal DESC)         AS rank
    FROM 
        tab employees
    INNER JOIN 
        tab managers
    ON 
        employees.mgr = managers.empno AND employees.deptno = managers.deptno
)
SELECT 
    deptno,
    diff,
    emp_name,
    mgr_name
FROM 
    emps_and_mgrs
WHERE 
    rank = 1

回复收藏 0 原文

~没有更多了~