oracle里Modle里[Any],SQL 使用Oracle的MODEL子句变换结果集

SQL 使用Oracle的MODEL子句变换结果集，就像本章第一个实例一样，你希望于熟知的常规变换技巧之外另辟蹊径。你想尝试一下 Oracle 的 MODEL 子句。不同于 SQL Server 的 PIVOT 操作符，Oracle 的 MODEL 子句并不是用来做结果集变换的。说得准确一些，用 MODEL 子句做结果集变换其实算是误用，它并不符合 MODEL 子句的设计意图。

SQL 使用Oracle的MODEL子句变换结果集 问题描述

就像本章第一个实例一样，你希望于熟知的常规变换技巧之外另辟蹊径。你想尝试一下 Oracle 的 MODEL 子句。不同于 SQL Server 的 PIVOT 操作符，Oracle 的 MODEL 子句并不是用来做结果集变换的。说得准确一些，用 MODEL 子句做结果集变换其实算是误用，它并不符合 MODEL 子句的设计意图。尽管如此，MODEL 子句还是为常见问题提供了一种有趣的思路。对于本例而言，你希望把下面的结果集：

做如下转换。

SQL 使用Oracle的MODEL子句变换结果集 解决方案

和常规的变换技巧一样，要在 MODEL 子句中使用聚合和 CASE 表达式。不同之处在于我们使用数组来存储聚合运算的值，并返回结果集里的数组。

SQL 使用Oracle的MODEL子句变换结果集 扩展知识

功能强大的 MODEL 子句是对 Oracle SQL 工具箱非常有益的补充。一旦开始使用 MODEL，我们一定会被它提供的各种实用的功能吸引住。比如迭代，以数组形式访问行值，向结果集 upsert1 行值的能力，以及构建参考模型的能力。虽然本实例不会用到 MODEL 子句的这些非常酷的功能，但(出于学习和研究的目的)从多种角度审视问题，并以出人意料的方式使用某些功能未尝不是一次有益的尝试。

理解本解决方案的第一步是，仔细观察 FROM 子句的内嵌视图。该内嵌视图用于统计 EMP 表中每个 DEPTNO 对应的员工总数。结果集显示如下。

以上结果集就是 MODEL 要处理的数据。仔细观察 MODEL 子句，可以发现它有 3 个组成部分：DIMENSION BY、MEASURES 和 RULES。先从 MEASURES 开始。

MEASURES 列表的项目就是我们为该查询声明的数组。该查询使用了 4 个数组：DEPTNO、D10、D20 和 D30。与 SELECT 列表类似，MEASURES 列表里的数组也可以指定别名。不难发现，上述 4 个数组中有 3 个实际上都来源于内嵌视图的 CNT。

如果说 MEASURES 列表包含了我们用到的数组，那么 DIMENSION BY 子句包含的项目则是数组的索引。试想一下，数组 D10 只是 CNT 的别名。再看一下上述内嵌视图的结果集，我们会发现 CNT 有 3 个值：3、5 和 6。当基于 CNT 值创建一个数组时，我们创建的是拥有 3 个元素的数组，即 3 个整数值：3、5 和 6。现在，我们该如何逐一地访问该数组的值呢？需要借助数组索引。由 DIMENSION BY 子句定义的索引值如下：10、20 和 30(来自上述结果集)。因此，以下面的表达式为例。

该表达式的评估结果为 3，该值就是数组 D10 中 DEPTNO 10 对应的 CNT 值(该值为 3)。

3 个数组(D10、D20、D30)都是基于 CNT 值创建的，因此它们都有相同的值。那么，如何把合适的统计值放入到正确的数组中呢？这是 RULES 子句要做的事。如果仔细观察上述内嵌视图的结果集的话，我们会看到 DEPTNO 的值分别为 10、20 和 30。RULES 子句里的 CASE 表达式只要逐一评估 DEPTNO 数组的每个值即可。

class= “第2级无序列表”>如果值为 10，为 D10[10] 存入 DEPTNO 10 对应的 CNT 值，否则存入 0。

如果值为 20，为 D20[20] 存入 DEPTNO 20 对应的 CNT 值，否则存入 0。

如果值为 30，为 D30[30] 存入 DEPTNO 30 对应的 CNT 值，否则存入 0。

如果你感到迷惑不解，请不要担心。我们接下来不妨先执行一下到目前为止讨论过的查询代码，下面是刚刚讨论过的查询代码对应的结果集。有时候读一段文字内容，然后实际执行一下对应的代码，最后再回过头去重新读一遍文字内容，这样理解起来会更容易。实际执行一下下面的代码之后，相信你就能迅速理解到目前为止我们讲过的内容。

如上所示，正是 RULES 子句改变了每一个数组里的值。如果你仍然感到疑惑不解，不妨实际执行一下如下所示的查询语句，该查询注释掉了 RULES 子句里的表达式，其他部分与上述查询相同。

现在应该足够清楚明白了，上述 MODEL 子句的结果集和内嵌视图完全相同，只是 COUNT 函数的返回值被分别指定了别名 D10、D20 和 D30。如下所示的查询也能证明这一点。

因此，MODEL 子句所做的事情就是取出 DEPTNO 和 CNT 的值，把它们放入数组，并确保每一个数组代表一个单独的 DEPTNO。现在，每一个数组 D10、D20 和 D30 都含有一个非零值，它们代表给定 DEPTNO 对应的 CNT。结果集变换已经完成，剩下的就是调用聚合函数 MAX 以返回单独的一行结果。(本书前几章已经多次使用 MIN 或 SUM，本实例的做法也出于同样的目的。)

1“upsert”的原意是“如果表中已经存在相关记录，则执行 UPDATE 操作；否则，执行 INSERT 操作”，但此处指的是对中间查询结果进行修改的操作，而不是针对物理上存在的表。——译者注