设为首页 - 加入收藏 PHP编程网 - PHP站长网 (http://www.52php.cn)- 电商,百科,编程,业界,移动互联,5G,云计算,站长网!
热搜: java 配置 原来 google
当前位置: 首页 > 站长百科 > 正文

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

发布时间:2021-05-16 02:16 所属栏目:[站长百科] 来源:网络整理
导读:我正在使用两个实体:Item和Attribute,它们类似于以下内容: Item----itemIdAttribute---------attributeIdname 项具有属性,如关联表中所指定: ItemAttribute--------------itemIdattributeId 当此数据到达客户端时,每个项目将显示一行,每行将按名称列出

我正在使用两个实体: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

【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

推荐文章
热点阅读