合并列

发布于 2024-12-11 06:48:50 字数 355 浏览 4 评论 0原文

我正在尝试执行以下操作：我有一张包含 ename、job、deptno 和 sal 的表。我正在尝试发起一个查询，返回每个部门的最高收入者。我已经通过分组和子查询完成了此操作。但是，我还想按部门显示平均萨尔。因此，结果如下：

"ename"  "dept"  "sal"  "average of dept"
sal      20      1000   500   
kelly    30      2000   800 
mika     40      3000   400

这可能是不可能的，因为平均值不与其他行关联。

任何建议将不胜感激。谢谢。我正在使用 Oracle 10g 来运行我的查询。

原文

I am trying to do the following:
I have a table with ename, job, deptno, and sal. I am trying to initiate a query that returns the top earners of each department. I have done this with grouping and a subquery. However, I also want to display the average sal by deptno. So the following would be the result:

"ename"  "dept"  "sal"  "average of dept"
sal      20      1000   500   
kelly    30      2000   800 
mika     40      3000   400

this might be impossible since the average does not associate with the other rows.

any suggestion would be appreciated. Thanks. I am using Oracle 10g to run my queries.

分享到QQ

分享到微博

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

发布评论

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

一江春梦 2024-12-18 06:48:50

您可以使用分析函数

WITH RankedAndAveraged AS (
  SELECT
    ename,
    dept,
    sal,
    RANK() OVER (PARTITION BY dept ORDER BY sal DESC) AS rnk,
    AVG(sal) OVER (PARTITION BY dept) AS "average of dept"
  FROM atable
)
SELECT
  ename,
  dept,
  sal,
  "average of dept"
FROM RankedAndAveraged
WHERE rnk = 1

：如果每个部门都具有相同的 sal 最大值，则每个部门可能会返回多名员工。如果您只想每个部门一个人，您可以将 RANK() 替换为 ROW_NUMBER()（在这种情况下，您还可以进一步扩展 ORDER BY通过指定附加排序标准来选择顶部项目，否则将从最高工资中随机选择）。

You could use analytic functions:

WITH RankedAndAveraged AS (
  SELECT
    ename,
    dept,
    sal,
    RANK() OVER (PARTITION BY dept ORDER BY sal DESC) AS rnk,
    AVG(sal) OVER (PARTITION BY dept) AS "average of dept"
  FROM atable
)
SELECT
  ename,
  dept,
  sal,
  "average of dept"
FROM RankedAndAveraged
WHERE rnk = 1

This may return more than one employee per department if all of them have the same maximum value of sal. You can replace RANK() with ROW_NUMBER() if you only want one person per department (in which case you could also further extend ORDER BY by specifying additional sorting criteria to pick the top item, otherwise it will be picked randomly from among those with the maximum salary).

回复收藏 0 原文

未蓝澄海的烟 2024-12-18 06:48:50

这应该有效。唯一的技巧是，如果一个部门中有几位最高薪的员工，它会显示所有员工。

SELECT t.ename, t.deptno, mx.sal as sal, mx.avg_sal as avg_sal
FROM tbl t, 
  (SELECT MAX(sal) AS sal, AVG(sal) AS avg_sal, deptno
   FROM tbl
   GROUP BY deptno) mx
WHERE t.deptno = mx.deptno AND t.sal = mx.sal

This should work. The only trick is that if you have several employees with the maximum salary in a department, it will show all of them.

SELECT t.ename, t.deptno, mx.sal as sal, mx.avg_sal as avg_sal
FROM tbl t, 
  (SELECT MAX(sal) AS sal, AVG(sal) AS avg_sal, deptno
   FROM tbl
   GROUP BY deptno) mx
WHERE t.deptno = mx.deptno AND t.sal = mx.sal

回复收藏 0 原文

场罚期间 2024-12-18 06:48:50

不确定 Oracle，大约 10 年没有使用过它，但类似这样的事情应该是可能的：

SELECT
  ename, deptno, sal,
  (SELECT AVG(T2.sal)
    FROM tbl T2
    WHERE T2.deptno = T.deptno
  ) AS average_of_dept
FROM tbl T
GROUP BY deptno
HAVING sal = MAX(sal)

Not sure about Oracle, haven't used it in about 10 years, but something like this should be possible:

SELECT
  ename, deptno, sal,
  (SELECT AVG(T2.sal)
    FROM tbl T2
    WHERE T2.deptno = T.deptno
  ) AS average_of_dept
FROM tbl T
GROUP BY deptno
HAVING sal = MAX(sal)

回复收藏 0 原文

~没有更多了~