Oracle SQL: collect 聚合函数

Question

我想通过某个属性对我的数据库条目进行分组，并且同时知道哪些条目在每个组中。 我使用Oracle COLLECT函数COLLECT函数来收集已分组的条目的id。

DECLARE
  TYPE ids_type IS TABLE OF number(19, 0);   
  ids ids_type;
BEGIN 
  select cast(collect(r.id) as ids_type) into ids from rechnungsdaten r group by r.status;
END;

但是我收到了错误信息：

ORA-06550：第5行，第44列：
PL / SQL：
ORA-00902：无效数据类型
ORA-06550：第5行，第5列：
PL / SQL：SQL语句被忽略
06550.00000 - "第%s行，第%s列：\n%s"
*原因：通常是PL / SQL编译错误。
*操作：

- Ewgenij Sokolovski

---

首先，为什么要声明 number(19,0) 类型。其次，您需要展示 rechnungsdaten 的样子（表定义）。 - FDavidov

我已经更正了，现在它是一个数字列表：TYPE ids_type IS TABLE OF number(19, 0); Rechnungsdaten有许多属性，但状态是varchar2（35 char），id是number（19, 0）。 - Ewgenij Sokolovski

嗯，在您的选择语句中，您试图将一个值（r.id）转换为表类型。当然，这是不合法的语句。 - FDavidov

我正在使用collect函数来完成它，就像在collect文档中描述的那样，这不正确吗？ - Ewgenij Sokolovski

如果你想要将另一张表的列内容填充到本地表，你可以直接使用INSERT INTO .... SELECT xyz FROM ...语句。在ORACLE中，你可以拥有嵌套表（即一张表的列本身也可以是一张表），我猜这就是COLLECT函数的主要用法（这只是我的猜测，可能会有误）。 - FDavidov

Answer 1

你不能在PL/SQL匿名块中声明的类型上使用COLLECT函数。你还有其他选择，比如创建一个数据库类型并运行你的收集查询。

create or replace TYPE ids_type IS TABLE OF number(19, 0);
SELECT
    r.status,
    CAST(COLLECT(r.id) AS ids_type)
FROM
    rechnungsdaten r
GROUP BY
    r.status;

使用简单的LISTAGG查询将id列表作为字符串显示

SELECT
    r.status,
    LISTAGG(r.id,',') WITHIN GROUP(
            ORDER BY
                id
        )
FROM
    rechnungsdaten r
GROUP BY
    r.status;

演示

- Kaushik Nayak