ANY_VALUE函数原是Mysql 5.7 中引入得到函数，在Oracle 19c中，也增加了该函数。该函数的作用是基于ORDER BY定义返回每个组中的任何值，并且通过该函数，可以消除将每个列都指定为GROUP BY子句的一部分的必要性。ANY_VALUE 也可以作为类似 MAX/MIN 的窗口函数发挥作用。

官方的说明如下：http://www.cndba.cn/dave/article/106646

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/any_value.html

http://www.cndba.cn/dave/article/106646

http://www.cndba.cn/dave/article/106646http://www.cndba.cn/dave/article/106646

Purpose

ANY_VALUE returns a single non-deterministic value of expr. You can use it as an aggregate function.
Use ANY_VALUE to optimize a query that has a GROUP BY clause. ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.
It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause. Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.http://www.cndba.cn/dave/article/106646

Semantics

ALL, DISTINCT: These keywords are supported by ANY_VALUE although they have no effect on the result of the query.
expr: The expression can be a column, constant, bind variable, or an expression involving them.
NULL values in the expression are ignored.
Supports all of the data types, except for LONG, LOB, FILE, or COLLECTION.
If you use LONG, ORA-00997 is raised.
If you use LOB, FILE, or COLLECTION data types, ORA-00932 is raised.
ANY_VALUE follows the same rules as MIN and MAX.
Returns any value within each group based on the GROUP BY specification. Returns NULL if all rows in the group have NULL expression values.
The result of ANY_VALUE is not deterministic.

http://www.cndba.cn/dave/article/106646

Restrictions

XMLType and ANYDATA are not supported.

http://www.cndba.cn/dave/article/106646

http://www.cndba.cn/dave/article/106646

官方给的示例：http://www.cndba.cn/dave/article/106646

/* Formatted on 2022/2/11 12:32:11 (QP5 v5.256.13226.35538) */
  SELECT c.cust_id, ANY_VALUE (cust_last_name), SUM (amount_sold)
    FROM customers c, sales s
   WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

CUST_ID  ANY_VALUE(CUST_LAST_NAME) SUM(AMOUNT_SOLD)
------- -------------------------- ----------------
   6950 Sandburg                                 78
  17920 Oliver                                 3201
  66800 Case                                   2024
  37280 Edwards                                2256
 109850 Lindegreen                              757
   3910 Oddell                                  185
  84700 Marker                                164.4
  26380 Remler                                  118
  11600 Oppy                                    158
  23030 Rothrock                                533
  42780 Zanis                                   182
...
630 rows selected.

官方的示例，看的不够明显，在看下面2个SQL 的对比：

/* Formatted on 2022/2/11 12:30:25 (QP5 v5.256.13226.35538) */
  SELECT d.deptno, d.dname, SUM (e.sal)
    FROM scott.dept d, scott.emp e
   WHERE e.deptno = d.deptno
GROUP BY d.deptno, d.dname


/* Formatted on 2022/2/11 12:30:33 (QP5 v5.256.13226.35538) */
  SELECT d.deptno, any_value (d.dname) AS DEPTNAME, SUM (e.sal)
    FROM scott.dept d, scott.emp e
   WHERE e.deptno = d.deptno
GROUP BY d.deptno

通过对比可以发现使用ANY_VALUE 之后，Group By之后就不再需要增加冗余的字段，SQL 再次变得优雅。

版权声明：本文为博主原创文章，未经博主允许不得转载。