sql – 有没有替代MULTISET运算符,以避免子查询？

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表：

CREATE TYPE SALES_TYPE IS TABLE OF SALE_TYPE;

我想通过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

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

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

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!