考虑一下Oracle的" emp"表。我想用"部门= 20"和"工作=职员"来获得薪水最高的员工。还假定没有" empno"列，并且主键涉及许多列。我们可以执行以下操作：

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。

解决方案

回答

以下内容经过了精心设计，但对于" top x"查询而言，这是一个很好的SQL模式。

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查询以获取最新价格

回答

那太棒了！我不知道我们可以将SELECT语句的结果与(x，y，z)进行比较。这对Oracle非常有用。

作为其他读者的附带说明，以上查询在"(deptno，job，sal)"之后缺少" ="。也许是Stack Overflow格式化程序将其吃掉了(？)。

再次感谢马克。

回答

如果可以确定目标数据库，则可以使用Mark Nold的解决方案，但是如果我们想要一些与方言无关的SQL *，请尝试

SELECT * 
FROM scott.emp e
WHERE e.deptno = 20 
AND e.job = 'CLERK'
AND e.sal = (
  SELECT MAX(e2.sal) 
  FROM scott.emp e2
  WHERE e.deptno = e2.deptno 
  AND e.job = e2.job
)

*我相信这应该在任何地方都有效，但是我没有测试它的环境。

回答

在Oracle中，我们还可以使用EXISTS语句，在某些情况下该语句更快。

例如...
选择姓名，电话号码
从客户
在哪里
(从大表中选择cust_id)
AND输入> SYSDATE -1
会很慢。

但
选择姓名，电话号码
来自客户
存在的地方
(从big_table那里选择cust_id cust_id = c.cust_id)
AND输入> SYSDATE -1
使用正确的索引将非常快。我们也可以将其与多个参数一起使用。

回答

有很多解决方案。我们还可以通过简单地添加表别名并加入列名来保持原始查询布局，我们一次查​​询中仍然只有DEPTNO = 20和JOB ='CLERK'。

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = 'CLERK'
  AND emptbl.SAL =  
    (
      select 
        max(salmax.SAL) 
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

还应注意，关键字" ALL"可用于这些类型的查询，这将使我们删除" MAX"功能。

SELECT 
  * 
FROM 
  scott.emp emptbl
WHERE
  emptbl.DEPTNO = 20 
  AND emptbl.JOB = 'CLERK'
  AND emptbl.SAL >= ALL  
    (
      select 
        salmax.SAL
      from 
        scott.emp salmax
      where 
        salmax.DEPTNO = emptbl.DEPTNO
        AND salmax.JOB = emptbl.JOB
    )

我希望这会有所帮助并且有意义。

回答

在Oracle中，我将使用解析函数来执行此操作，因此我们只需查询emp表一次：

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER () AS max_sal
          FROM scott.emp e
         WHERE deptno = 20 
           AND job = 'CLERK')
 WHERE sal = max_sal

它更简单，更易于阅读并且更高效。

如果要修改它以列出所有部门的此信息，则需要在OVER中使用" PARTITION BY"子句：

SELECT *
  FROM (SELECT e.*, MAX (sal) OVER (PARTITION BY deptno) AS max_sal
          FROM scott.emp e
         WHERE job = 'CLERK')
 WHERE sal = max_sal
ORDER BY deptno