在没有LISTAGG的Oracle SQL中按字符串列表排序

我正在使用两个实体：Item和Attribute,它们类似于以下内容：

Item
----
itemId

Attribute
---------
attributeId
name

项具有属性,如关联表中所指定：

ItemAttribute
--------------
itemId
attributeId

当此数据到达客户端时,每个项目将显示一行,每行将按名称列出属性列表.例如：

Item  Attributes
----  ----------
1     A,B,C
2     A,C
3     A,B

用户可以选择对“属性”列进行排序,因此我们需要能够按如下方式对数据进行排序：

Item  Attributes
----  ----------
3     A,B
1     A,C

目前,我们每个ItemAttribute行获取一行数据.基本上：

SELECT Item.itemId,Attribute.name
    FROM Item
    JOIN ItemAttribute
      ON ItemAttribute.itemId = Item.itemId
    JOIN Attribute
      ON Attribute.attributeId = ItemAttribute.attributeId
ORDER BY Item.itemId;

产生如下结果：

itemId  name
------  ----
1       A
1       B
1       C
2       A
2       C
3       A
3       B

实际的ORDER BY子句基于用户输入.它通常是单列,因此排序很简单,处理结果集的应用程序端循环将属性名称组合成逗号分隔列表,以便在客户端上显示.但是当用户要求对该列表进行排序时,让Oracle对结果进行排序以便 – 使用上面的示例 – 我们得到：

itemId  name
------  ----
3       A
3       B
1       A
1       B
1       C
2       A
2       C

Oracle的LISTAGG函数可用于在排序之前生成属性列表;但是Attribute.name可以是一个非常长的字符串,并且组合列表可能超过4000个字符,这将导致查询失败.

有没有一种干净,有效的方法使用Oracle SQL(11gR2)以这种方式对数据进行排序？

解决方法

这里有两个问题：

1)如何聚合超过4000个字符的数据

汇总如此多的数据并将其显示在一个列中甚至是明智的吗？

无论如何,你需要某种大型结构来显示超过4000个字符,例如CLOB.您可以按照Tom Kyte’s thread之一中描述的一般准则编写自己的聚合方法(显然您需要修改它以使最终输出为CLOB).

我将使用嵌套表和自定义函数演示一个更简单的方法(适用于10g)：

SQL> CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS
  2     l_result CLOB;
  3  BEGIN
  4     FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP
  5        l_result := l_result ||' '|| cc.column_value;
  6     END LOOP;
  7     return l_result;
  8  END;
  9  /

Function created.

SQL> SELECT item,2         concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  3    FROM data
  4   GROUP BY item;

ITEM ATTRIBUTES
1    a b c
2    a c
3    a b

2)如何对大数据进行排序

不幸的是,您无法按Oracle中任意大的列进行排序：相对于排序键的类型和长度存在已知的限制.

>尝试使用clob排序将导致ORA-00932：不一致的数据类型：expected – 得到CLOB.
>尝试使用大于数据库块大小的键进行排序(例如,如果您决定将大数据拆分为多个VARCHAR2)将产生ORA-06502：PL / SQL：数值或值错误：字符串缓冲区太小

我建议你按属性列的前4000个字节排序：

SQL> SELECT * FROM (
  2     SELECT item,3            concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  4       FROM data
  5      GROUP BY item
  6  ) order by dbms_lob.substr(attributes,4000,1);

ITEM ATTRIBUTES
3    a b
1    a b c
2    a c