1234SELECT * FROM scott.emp
WHERE"/>

关于 oracle:SQL:聚合函数和分组依据

关于 oracle:SQL:聚合函数和分组依据

SQL: aggregate function and group by

考虑 Oracle emp 表。我想用 department = 20job = clerk 获得最高薪水的员工。还假设没有"empno"列,并且主键涉及许多列。你可以这样做:

1
2
3
4
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 模式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 Query to get latest price


如果我确定目标数据库,我会选择 Mark Nold 的解决方案,但如果您想要一些与方言无关的 SQL*,请尝试

1
2
3
4
5
6
7
8
9
10
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 中,我会用一个分析函数来做,所以你只需要查询一次 emp 表:

1
2
3
4
5
6
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" 子句:

1
2
3
4
5
6
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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"函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 中,您还可以使用 EXISTS 语句,这在某些情况下更快。

例如...
选择姓名、号码
来自客户
客户在哪里
(从大表中选择 cust_id)
并输入 SYSDATE -1
会很慢。

但是
选择姓名、号码
来自客户 c
存在于何处
(从大表中选择 cust_id,其中 cust_id=c.cust_id)
并输入 SYSDATE -1
使用适当的索引会非常快。您也可以将其与多个参数一起使用。


那太好了!我不知道您可以将 (x, y, z) 与 SELECT 语句的结果进行比较。这适用于 Oracle。

作为其他读者的旁注,上述查询在 "(deptno,job,sal)" 之后缺少一个 "="。也许堆栈溢出格式化程序吃了它(?)。

再次感谢马克。


推荐阅读