假设您有一个表格如下：
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
解决方法
在Oracle 10g中,您可以使用COLLECT运算符,如下所示：
select employee_id,cast(collect(sale_type(product_id,sale_amount)) as sales_type) from employee_sales group by employee_id
