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

Suppose you have a table as follows:

假设您有一个表格如下:

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

And suppose it is populated as follows:

并假设它填充如下:

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

Now, suppose I create a custom type named SALE_TYPE which represents a (PRODUCT_ID, SALE_AMOUNT) tuple:

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

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

And suppose I also create a custom type named SALES_TYPE which represents a TABLE of SALE_TYPE:

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

CREATE TYPE SALES_TYPE IS TABLE OF SALE_TYPE;

I want to query the EMPLOYEE_SALES table group by EMPLOYEE_ID. For each EMPLOYEE_ID, I wish to create a SALES_TYPE containing that employee's sales. According to the documentation, I would do something like this:

我想通过EMPLOYEE_ID查询EMPLOYEE_SALES表组。对于每个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

I expect this query to yield something like this:

我希望这个查询产生这样的东西:

+-------------+------------------------------+
| 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 | |
|             | +------------+-------------+ |
+-------------+------------------------------+

Is there a way to achieve the same results without issuing a sub-query (the real query I am working on is much more complicated than this contrived example)? For example, is there something like this:

有没有办法在不发出子查询的情况下获得相同的结果(我正在处理的真实查询比这个人为的例子复杂得多)?例如,有这样的事情:

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

2 个解决方案

#1

In Oracle 10g, you can use the COLLECT operator as follows:

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

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

#2

You should be able to write a custom aggregate function along the lines of the aggregate functions that were created to concatenate strings before 11.2 and the LISTAGG function.

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

For example, I can create an EMP_OBJ object and a collection of those objects (EMP_TBL) and write a custom aggregate function that generates an EMP_TBL collection from a simple SQL statement

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

Create the basic types

创建基本类型

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.

Create the type that we'll use to do the aggregation

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

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.

Declare the aggregate function

声明聚合函数

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

Function created.

And now a simple GROUP BY using our custom aggregate function will generate an EMP_TBL collection

现在,使用我们的自定义聚合函数的简单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'))