oracle多行记录合并连接聚合字符串的几种方法（Several methods for merging and connecting aggregated strings in Oracle multiline records）

oracle多行记录合并连接聚合字符串的几种方法（Several methods for merging and connecting aggregated strings in Oracle multiline records） oracle多行记录合并连接聚合字符串的几种方法(Several methods for merging and connecting aggregated strings in Oracle multiline records) Oracle several ways to record merge / join / aggregation strings [turn]2008-09-13 11:32 How to merge a string with multiple lines of records has been one of the SQL questions that novice Oracle loves to ask. I've seen no more than 30 posts on this subject, and now I'll make a summary of the problem. What is a merged multiline string (connection string), for example?: SQL> desc test; Name, Type, Nullable, Default, Comments ------------------------------------------ COUNTRY VARCHAR2 (20) Y CITY VARCHAR2 (20) Y SQL> select * from test; COUNTRY CITY ---------------------------------------- China Taipei China Hongkong Shanghai China Tokyo, Japan Osaka The following set of results is required: --------------------------- China, Taipei, Hongkong, Shanghai Tokyo, Japan, Osaka Actually, it's a aggregation of characters, and I wonder why Oracle didn't provide the official aggregate function to implement it:) Analyze the following solution several of the often mentioned (one assumes the highest evaluation standard.): 1. field collection small and fixed. The flexibility performance assumes the difficulty. The principle of this method is that you already know how many CITY field values are, and not too much. If too many, the SQL would be pretty good Long.. See examples: SQL> select t.country, 2 MAX (decode (t.city, 'Taipei', t.city||'', NULL) ||) 3 MAX (decode (t.city, 'Hongkong', t.city||'', NULL) ||) 4 MAX (decode (t.city, 'Shanghai', t.city||'', NULL) ||) 5 MAX (decode (t.city, 'Tokyo', t.city||'', NULL) ||) 6 MAX (decode (t.city, Osaka, t.city||', NULL).) 7, from, test, t, GROUP, BY, t.country 8 / COUNTRY MAX (DECODE (T.CITY), 'Taipei', T.CIT -------------------------------------------------- China, Taipei, Hongkong, Shanghai, Tokyo, Japan, Osaka, As soon as you look at it, you'll see. (if you don't understand, take a good tutorial, MAX, DECODE, and grouping). This method is the most stupid method But for some applications, perhaps the most effective way is for it. Table 2. fixed fixed field function method. The performance flexibility assumes the difficulty. This method must know which table is in advance, that is to say, a table has to write a function, but the value of method 1 is much more convenient. In most applications, there is no need for a large number of such merge strings. Nonsense, look below: Define a function Create, or, replace, function, str_list (str_in, in, VARCHAR2) -- categorical fields Return VARCHAR2 Is Str_list VARCHAR2 (4000) default null; - - string after connection STR VARCHAR2 (20) default null; - join symbol Begin For, x, in (select, TEST.CITY, from, TEST, where, TEST.COUNTRY = str_in) loop Str_list: = str_list str to_char (x.city) || ||; STR: = ','; End loop; Return str_list; End; Use: SQL>, select, DISTINCT (T.country), list_func1 (t.country), from, test, t; COUNTRY LIST_FUNC1 (T.COUNTRY) ------------------------------------ China, Taipei, Hongkong, Shanghai Tokyo, Japan, Osaka SQL>, select, t.country, str_list (t.country), from, test, t, GROUP,, BY, t.country; COUNTRY STR_LIST (T.COUNTRY) ------------------------------------------- China, Taipei, Hongkong, Shanghai Tokyo, Japan, Osaka At this point, the use of grouping and finding unique requirements can be met. The principle is that, according to the unique grouping field, country, Query again all the merged columns corresponding to the field in the function, and merge the output using PL/SQL. 3. flexible table function method. * * performance flexibility. This difficulty. On the basis of method 2, this method uses dynamic SQL to import table name and field name, so as to achieve flexible purpose. Create, or, replace, function, str_list2 (key_name, in, VARCHAR2), Key in varchar2, Coname in varchar2, Tname, in, VARCHAR2) Return VARCHAR2 As Type, RC, is, ref, cursor; STR VARCHAR2 (4000); SEP VARCHAR2 (2); Val VARCHAR2 (4000); Cur rc; Begin Open, cur, for,'select,'||coname||' From tname'|| || ' Where 'key_name' = || ||: X ' Using key; Loop Fetch cur into val; Exit when cur%notfound; STR: = STR SEP val || ||; SEP: = ','; End loop; Close cur; Return str; End; SQL> select test.country, 2 str_list2 ('COUNTRY', test.country,'CITY','TEST') emplist 3 from test 4 group by test.country 5 / COUNTRY EMPLIST Similiarly China, Taipei, Hongkong, Shanghai Tokyo, Japan, Osaka 4. a SQL method assumes the flexibility performance assumes the difficulty. A SQL is a master of this, we have never sought the problem of a SQL method in a certain period of time, but the master seems to have been misinterpreted, a lot of poor performance, poor readability, poor flexibility SQL is the product of the principle, the so-called tiger painting is not anti a dog. However, solving the problem is always the first principle, and here is a more representative SQL method. SELECT, country, max (substr (city, 2)) city FROM (SELECT, country, sys_connect_by_path (city, `, ') city FROM (SELECT, country, city, country||rn, rchild, country|| (RN-1) rfather FROM (SELECT, test.country, test.city, row_number () over (PARTITION, BY, test.country, ORDER, BY) Test.city) Rn FROM, test) CONNECT, BY, PRIOR, rchild=rfather, START, WITH,, rfather, LIKE, and'%0') GROUP BY country; The following step parsing, there are 4 FROM, there are 4 results set operations. Step 1 adds the serial number RN to the record SQL>, SELECT, test.country, test.city, row_number () over (PARTITION, BY, test.country, ORDER) BY test.city) Rn 2 FROM test 3 / COUNTRY CITY RN -------------------------------------------------- Osaka, Japan 1 Tokyo, Japan 2 Shanghai, China 1 Taipei, China 2 Hongkong, China 3 Step 2 creates the parent node of the child node SQL>, SELECT, country, city, country||rn, rchild, country|| (RN-1) rfather 2 FROM 3 (SELECT, test.country, test.city, row_number () over (PARTITION, BY, test.country, ORDER) BY test.city) Rn 4, FROM, test) 5 / Japan, Osaka, Japan, 1, Japan, 0 Japan, Tokyo, Japan, 2, Japan, 1 Shanghai, China, 1, China 0 Taipei, China, 2, China 1 Hongkong, China, 3, China 2 Step 3 generates a result set using sys_connect_by_path SELECT, country, sys_connect_by_path (city, ',') city FROM (SELECT, country, city, country||rn, rchild, country|| (RN-1) rfather FROM (SELECT test. country, test.city, row _ number () over (partition by test.country order city test.city) rn from test)) connect city prior rchild = rfather start with rfather like '% 0' 日本, 大阪 日本, 大阪, 东京 中国, 上海 中国, 上海, 台北 中国, 上海, 台北, 香港 step 4 最终步骤, 筛选结果集合 sql > select country, max (substr (city), city 2 from 3 (select country, stitches _ connect _ city _ path (city, 'the city') 4 from 5 (select country, city, country | | rn rchild, country | | (rn - 1) rfather 6 from 7 (select test.country, test.city, row _ number () over (partition by test.country order city test.city) rn 8 from test)) 9 connect city prior rchild = rfather start with rfather like '% 0') 10 group by country; country city -------------------- ------- 中国 上海, 台北, 香港 日本 大阪, 东京 可谓是, 7歪8搞, 最后还是弄出来了, 呵呵. 5.自定义聚合函数 灵活性????? 性能????? 难度 ????? 最后一个方法是我认为 "王道" 的方法, 自定义聚合函数. 就如何我在本开始说的, 为啥oracle没有这种聚合函数呢? 我也不 知道, 但oracle提供了聚合函数的 api可以让我方便的自己定义聚合函数. 详细可以看oracle data catridge guide这个文档.连接如下: http: / / www.oracle.com.cn / other / 9ionlinedoc / appdev.920 / a96595 / toc.htm 下面给出一个简单的例子: sql > select t.country, strcat (t.city) from t group by t.country test; country strcat (t.city) -------------------- ------------------ 日本 东京, 大阪 中国 台北, 香港, 上海 简单吧, 和官方的函数一样的便捷高效. 函数: create or replace function strcat (input varchar2) return varchar2 parallel _ enable aggregate using strcat _ type; type: create or replace type strcat _ type as object ( cat _ string varchar2 (4000) static function odciaggregateinitialize (cs _ ctx in out strcat _ type) return number member function odciaggregateiterate (self in out strcat _ type value in varchar2) return number member function odciaggregatemerge (self in out strcat _ type ctx2 in out strcat _ type) return number member function odciaggregateterminate (self in out strcat _ type returnvalue out varchar2, flags in number) return number ) 6.待发掘... 总结, 合并字符串还有更多的方法希望大家能发掘, 本文的目的主要是抛砖引玉, 如果有新的发现我会继续更新方法.需要注意的问题是, 本文采用varchar2为例子, 所以长度有限制, oracle的版本对方法的实现也影响