代码之家 › 专栏 › 技术社区 › filippo

先前Oracle连接的最大大小

varchar2 permutation oracle

1

filippo · 技术社区 · 15 年前

我有人帮我翻桌子:

Col
23
25
15
53
...

23,25,15,53...

这样做的查询是

SELECT max(ltrim(sys_connect_by_path(flow_run_id, ','), ','))
FROM
    (select flow_run_id, rownum rn
    from table
    where CREATED_DATE < sysdate - 32
    and flow_id = 3
    order by 1 desc)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1

(这个美丽是米迦勒给的 in here )

我目前的问题是结果太长(ORA-01489超过了varchar2的4k个字符)。我还在学习这些 sys_connected_by_path 所以我需要帮忙整理一下。如何使此查询返回多行而不是一个超长行?即。:

419,1,2,3,411,418,4,415,887,413,414,201,888,890,401,417,610,412,416,5,6,922,1080,1422,1423,1411,1412,1413,1414,1415,1416,1417,1418,1419,1964,2217,1636,2037,1988,1970,2038,1989,2000,2040,1993,2043,1994,2001,2044,1658,1995,2045,2224,1996,2019,1678,1997,2022,2201,1680,2219,2024,2207,1677,2209,2220,1959,2211,1961,2026,2212,1962,2028,2215,1675,1676,2035,2216,1986,1963,2017,1983,1935,2002,2018,1985,1936,2003,2020,2032,1937,2004,2021,2033,1938,1943,2023,2034,1939,1944,2025,2225,1941,1950,2027,2036,1942,1955,2029,2041,1945,1956,2030,2227,1946,1957,2031,2039,1947,2005,1974,2042,1948,2006,1976,2228,1949,2007,1978,1951,2009,1979,1929,1952,2012,1980,1931,1953,2013,1981,1933,1954,2015,2334,2350,2311,2239,2240,2241,2242,2245,2246,2249,2250,2336,2312,2008,2010,2011,2014,2251,2253,2016,2243,2244,2247,2351,2248,(...)

得到

419,1,2,3,411,418,4,415,887,413,414,201,888,890,401,417,610,412,416,5,6,922,1080
1423,1411,1412,1413,1414,1415,1416,1417,1418,1419,1964,2217,1636,2037,1988,1970,2038
2000,2040,1993,2043,1994,2001,2044,1658,1995,2045,2224,1996,2019,1678,1997,2022,2201
(...)

有什么建议吗?

f。

2 回复 | 直到 9 年前

1

2

Vincent Malgrat 15 年前

下面的查询将把大字符串切分为几个部分:

SQL> SELECT root_rn, MAX(concat)
  2    FROM (SELECT connect_by_root(rn) root_rn,
  3                 ltrim(sys_connect_by_path(flow_run_id, ','), ',') concat
  4             FROM (SELECT flow_run_id, rownum rn
  5                      FROM (SELECT round(dbms_random.VALUE(1, 10000))
  6                                    AS flow_run_id
  7                              FROM dual
  8                           CONNECT BY ROWNUM <= 2000)
  9                     ORDER BY 1 DESC)
 10            START WITH MOD(rn, 10) = 1
 11           CONNECT BY PRIOR rn = rn - 1
 12                  AND MOD(rn, 10) != 1)
 13   GROUP BY root_rn
 14   ORDER BY root_rn;

   ROOT_RN MAX(CONCAT)
---------- -------------------------------------------------------------------
         1 654,6710,5297,5481,5085,2793,7646,9170,1051,2387
        11 1882,8285,5430,4928,267,3779,3843,1151,3085,1446
        21 4721,6087,6755,9904,805,2776,4633,2772,7785,5818
        31 5189,5307,6481,2099,3832,9788,5970,8068,6605,3904
        41 53,7013,1314,7717,9320,7069,907,5367,5013,7637
        51 3903,2318,2611,7954,5751,5598,6148,6555,9724,984
       [...]

如果希望每行有更多的元素,可以用更大的数字替换“10”。

2

1

Nahuel Fouilleul 14 年前

为了维持秩序有些小改动

SELECT 10*frn+1 root,ltrim(sys_connect_by_path(flow_run_id,','),',') FROM 
  (SELECT flow_run_id,mod(rn,10) mrn,floor(rn/10) frn,count(*)over(partition by floor(rn/10))-1 crn FROM
    (SELECT flow_run_id, row_number()over(order by flow_run_id)-1 rn FROM
      (SELECT round(dbms_random.VALUE(1, 10000)) AS flow_run_id FROM dual CONNECT BY ROWNUM <= 2000
      )
    )
  )
WHERE crn = mrn
START WITH mrn = 0
CONNECT BY PRIOR mrn = mrn-1 AND PRIOR frn = frn