SQL:聚合函数和分组依据 [英] SQL: aggregate function and group by

问题描述

考虑Oracle emp表.我想用department = 20和job = clerk来获得薪水最高的员工.还假定没有"empno"列，并且主键涉及许多列.您可以执行以下操作:

Consider the Oracle emp table. I'd like to get the employees with the top salary with department = 20 and job = clerk. Also assume that there is no "empno" column, and that the primary key involves a number of columns. You can do this with:

select * from scott.emp
where deptno = 20 and job = 'CLERK'
and sal =  (select max(sal) from scott.emp
            where deptno = 20 and job = 'CLERK')

这可行，但是我必须重复测试deptno = 20和job ='CLERK'，这是我想避免的.是否有更优雅的方式编写此代码，也许使用group by?顺便说一句，如果这很重要，我正在使用Oracle.

This works, but I have to duplicate the test deptno = 20 and job = 'CLERK', which I would like to avoid. Is there a more elegant way to write this, maybe using a group by? BTW, if this matters, I am using Oracle.

推荐答案

以下内容有些过分设计，但是对于前x个"查询来说，这是一个很好的SQL模式.

The following is slightly over-engineered, but is a good SQL pattern for "top x" queries.

SELECT 
 * 
FROM 
 scott.emp
WHERE 
 (deptno,job,sal) IN
 (SELECT 
   deptno,
   job,
   max(sal) 
  FROM 
   scott.emp
  WHERE 
   deptno = 20 
   and job = 'CLERK'
  GROUP BY 
   deptno,
   job
  )

还请注意，这将在Oracle和Postgress(我认为)中有效，但不适用于MS SQL.对于MS SQL中的类似内容，请参见问题 SQL查询以获取最新价格

Also note that this will work in Oracle and Postgress (i think) but not MS SQL. For something similar in MS SQL see question SQL Query to get latest price

这篇关于SQL:聚合函数和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！