sql - 是否有避免子查询的 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  |+-------------+------------+-------------+

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

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

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

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

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

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