是否有 MULTISET 运算符的替代方案可以避免子查询？

Question

假设您有一个如下表：

CREATE TABLE EMPLOYEE_SALES
(
    EMPLOYEE_ID NUMBER,
    PRODUCT_ID  NUMBER,
    SALE_AMOUNT NUMBER
);

假设它的填充方式如下：

+-------------+------------+-------------+
| EMPLOYEE_ID | PRODUCT_ID | SALE_AMOUNT |
+-------------+------------+-------------+
|      1      |     100    |       1.05  |
|      1      |     200    |      45.67  |
|      2      |     100    |       3.25  |
|      2      |     200    |      34.29  |
+-------------+------------+-------------+

现在，假设我创建一个名为 SALE_TYPE 的自定义类型，它表示 (PRODUCT_ID, SALE_AMOUNT) 元组：

CREATE TYPE SALE_TYPE IS OBJECT
(
  PRODUCT_ID  NUMBER,
  SALE_AMOUNT NUMBER
);

假设我还创建了一个名为 SALES_TYPE 的自定义类型，它表示 SALE_TYPE 的 TABLE：

CREATE TYPE SALES_TYPE IS TABLE OF SALE_TYPE;

我想查询 EMPLOYEE_SALES > 按 EMPLOYEE_ID 分组。对于每个 EMPLOYEE_ID，我希望创建一个包含该员工销售额的 SALES_TYPE。根据文档，我会做这样的事情：

SELECT
  EMPLOYEE_ID,
  CAST
  (
    MULTISET
    (
      SELECT
        PRODUCT_ID,
        SALE_AMOUNT
      FROM
        EMPLOYEE_SALES inner_employee_sales
      WHERE
        inner_employee_sales.employee_id = employee_sales.employee_id
    ) AS SALES_TYPE
  ) AS SALES
FROM
  EMPLOYEE_SALES
GROUP BY
  EMPLOYEE_ID

我希望这个查询产生这样的结果：

+-------------+------------------------------+
| EMPLOYEE_ID | SALES                        |
+-------------+------------------------------+
|     1       | +------------+-------------+ |
|             | | PRODUCT_ID | SALE_AMOUNT | |
|             | +------------+-------------+ |
|             | |    100     |        1.05 | |
|             | |    200     |       45.67 | |
|             | +------------+-------------+ |
+-------------+------------------------------+
|     2       | +------------+-------------+ |
|             | | PRODUCT_ID | SALE_AMOUNT | |
|             | +------------+-------------+ |
|             | |    100     |        3.25 | |
|             | |    200     |       34.29 | |
|             | +------------+-------------+ |
+-------------+------------------------------+

有没有办法在不发出子查询的情况下获得相同的结果（我是真实查询工作比这个人为的例子复杂得多）？例如，有没有这样的事情：

SELECT
  EMPLOYEE_ID,
  CAST
  (
    COLLECT_ALL_RECORDS_WITHIN_THE_CURRENT_GROUP(
      PRODUCT_ID,
      SALE_AMOUNT
    )
    AS SALES_TYPE
  ) AS SALES
FROM
  EMPLOYEE_SALES
GROUP BY
  EMPLOYEE_ID

Answer 1

在 Oracle 10g 中，您可以使用 COLLECT 运算符，如下所示：

select employee_id, 
       cast(collect(sale_type(product_id, sale_amount)) as sales_type)
from employee_sales
group by employee_id

Answer 2

您应该能够编写自定义聚合函数 11.2 之前创建的用于连接字符串的聚合函数和 LISTAGG 函数。

例如，我可以创建一个 EMP_OBJ 对象和这些对象的集合 (EMP_TBL)，并编写一个自定义聚合函数，该函数从简单的 SQL 语句生成 EMP_TBL 集合

创建基本类型

SQL> create type emp_obj as object (
  2    empno number,
  3    ename varchar2(100)
  4  );
  5  /

Type created.

SQL> create type emp_tbl
  2  as
  3  table of emp_obj;
  4  /

Type created.

创建我们将用于进行聚合的类型

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TYPE emp_tbl_agg AS OBJECT
  2  (
  3    g_emp_coll emp_tbl,
  4    STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  emp_tbl_agg )
  5      RETURN NUMBER,
  6    MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  emp_tbl_agg ,
  7                                         value  IN      emp_obj)
  8       RETURN NUMBER,
  9    MEMBER FUNCTION ODCIAggregateTerminate(self         IN   emp_tbl_agg,
 10                                           returnValue  OUT  emp_tbl,
 11                                           flags        IN   NUMBER)
 12      RETURN NUMBER,
 13    MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  emp_tbl_agg,
 14                                       ctx2  IN      emp_tbl_agg)
 15      RETURN NUMBER
 16* );
 17  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TYPE BODY emp_tbl_agg IS
  2    STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  emp_tbl_agg)
  3      RETURN NUMBER IS
  4    BEGIN
  5      sctx := emp_tbl_agg(NULL);
  6      sctx.g_emp_coll := new emp_tbl();
  7      RETURN ODCIConst.Success;
  8    END;
  9    MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  emp_tbl_agg,
 10                                         value  IN      emp_obj )
 11      RETURN NUMBER IS
 12    BEGIN
 13      SELF.g_emp_coll.extend();
 14      SELF.g_emp_coll(self.g_emp_coll.count) := value;
 15      RETURN ODCIConst.Success;
 16    END;
 17    MEMBER FUNCTION ODCIAggregateTerminate(self         IN   emp_tbl_agg,
 18                                           returnValue  OUT  emp_tbl,
 19                                           flags        IN   NUMBER)
 20      RETURN NUMBER IS
 21    BEGIN
 22      returnValue := self.g_emp_coll;
 23      RETURN ODCIConst.Success;
 24    END;
 25    MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  emp_tbl_agg,
 26                                       ctx2  IN      emp_tbl_agg)
 27      RETURN NUMBER IS
 28    BEGIN
 29      FOR i IN ctx2.g_emp_coll.FIRST .. ctx2.g_emp_coll.LAST
 30      LOOP
 31        self.g_emp_coll.extend();
 32        self.g_emp_coll(self.g_emp_coll.count) := ctx2.g_emp_coll(i);
 33      END LOOP;
 34      RETURN ODCIConst.Success;
 35    END;
 36* END;
SQL> /

Type body created.

声明聚合函数

SQL> create or replace function emp_agg( p_input emp_obj )
  2    return emp_tbl
  3    aggregate using emp_tbl_agg;
  4  /

Function created.

现在，使用我们自定义聚合函数的简单 GROUP BY 将生成 EMP_TBL 集合

SQL> select deptno, emp_agg( emp_obj( empno, ename ) )
  2    from emp
  3   group by deptno;

    DEPTNO
----------
EMP_AGG(EMP_OBJ(EMPNO,ENAME))(EMPNO, ENAME)
--------------------------------------------------------------------------------

        10
EMP_TBL(EMP_OBJ(7782, 'CLARK'), EMP_OBJ(7934, 'MILLER'), EMP_OBJ(7839, 'KING'))

        20
EMP_TBL(EMP_OBJ(7369, 'smith'), EMP_OBJ(7902, 'FORD'), EMP_OBJ(7876, 'ADAMS'), E

MP_OBJ(7788, 'SCOTT'), EMP_OBJ(7566, 'JONES'))

        30
EMP_TBL(EMP_OBJ(7499, 'ALLEN'), EMP_OBJ(7900, 'SM2'), EMP_OBJ(7844, 'TURNER'), E


    DEPTNO
----------
EMP_AGG(EMP_OBJ(EMPNO,ENAME))(EMPNO, ENAME)
--------------------------------------------------------------------------------

MP_OBJ(7698, 'BLAKE'), EMP_OBJ(7654, 'MARTIN'), EMP_OBJ(7521, 'WARD'))