将预言机列值拆分为逗号分隔的值

Question

我有一个表，其中列在预言机中值为 5我想将列的值更改为值数组，例如 [5,4,3,2,1]表示差值为 1

Answer 1

这是"分层查询"的直接应用（如果不熟悉，请参阅 Oracle 文档）。它使用 sys_connect_by_path 来创建值列表（方括号添加在分层查询之外 - sys_connect_by_path生成的前导逗号需要先用 ltrim() 砍掉

）。

with
     test_data ( id, val ) as (
       select 101, 5 from dual union all
       select 103, 3 from dual
     )
-- End of made-up test data; NOT part of the query.
-- SQL query begins BELOW THIS LINE. Change table and column names as needed.
select id, val,
       '[' || ltrim(sys_connect_by_path(val - level + 1, ','), ',') || ']' as val_list
from   test_data
where  connect_by_isleaf = 1
connect by level <= val
       and prior id = id
       and prior sys_guid() is not null
;
 ID  VAL  VAL_LIST
---  ---  -----------
101    5  [5,4,3,2,1]
103    3  [3,2,1]

Answer 2

对我来说

，最简单的方法是为此创建函数

CREATE OR REPLACE FUNCTION toArray (p_value NUMBER)
   RETURN VARCHAR
AS
   l_result   VARCHAR (4000) := '[';
BEGIN
   FOR i IN REVERSE 2 .. p_value
   LOOP
      l_result := l_result || i || ',';
   END LOOP;
   IF (p_value >= 1)
   THEN
      l_result := l_result || 1;
   END IF;
   l_result := l_result || ']';
   RETURN l_result;
END;

查询select toArray(5) from dual 返回结果 [5,4,3,2,1]

Answer 3

我会建议一种更简单的使用方法。希望下面的片段有所帮助。这里唯一的缺点是我能建议的是 LISTAGG 仅适用于 oracle 版本 11 及更高版本。因此，它在以下版本中不起作用。

SELECT listagg(a.lvl, ',') within GROUP (
ORDER BY a.lvl DESC) somedata
from
  (SELECT '&Enter_val'+1 - level lvl,
    1 lvl2
  from dual
    CONNECT BY level < '&Enter_val'+1
  ORDER BY lvl DESC
  )a;