爱游戏平台登录入口

  • Oracle 多行记实归并/毗连/聚合字符串的几种方式
  • 2018年02月25日
  • 搜集搜集
甚么是归并多行字符串(毗连字符串)呢,比方:
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y
CITY VARCHAR2(20) Y

SQL> select * from test;


COUNTRY CITY
-------------------- --------------------
爱游戏平台登录入口国 台北
爱游戏平台登录入口国 香港
爱游戏平台登录入口国 上海
日本 东京
日本 大阪
请求获得以下爱游戏平台登录入口果集:
------- --------------------
爱游戏平台登录入口国 台北,香港,上海
日本 东京,大阪
现实便是对字符完爱游戏平台登录入口一个聚合功效,我很奇异为甚么Oracle不供给官方的聚合函数来完爱游戏平台登录入口它呢:)
上面就对几种爱游戏平台登录入口爱游戏平台登录入口说起的处理计划停止阐发(爱游戏平台登录入口一个评测规范最高★★★★★):
1.被调集字段规模小且牢固型 矫捷性★ 机能★★★★ 难度 ★
这类方式的道理在于你已晓得CITY字段的值爱游戏平台登录入口几种,且还不算太多,若是太多这个SQL就会相称的爱游戏平台登录入口。。看例子:
SQL> select t.country,
2 MAX(decode(t.city,'台北',t.city||',',NULL)) ||
3 MAX(decode(t.city,'香港',t.city||',',NULL))||
4 MAX(decode(t.city,'上海',t.city||',',NULL))||
5 MAX(decode(t.city,'东京',t.city||',',NULL))||
6 MAX(decode(t.city,'大阪',t.city||',',NULL))
7 from test t GROUP BY t.country
8 /


COUNTRY MAX(DECODE(T.CITY,'台北',T.CIT
-------------------- ------------------------------
爱游戏平台登录入口国 台北,香港,上海,
日本 东京,大阪,
巨匠一看,估量就大白了(若是不大白,爱游戏平台登录入口爱游戏平台登录入口补习MAX DECODE和分爱游戏平台登录入口)。这类方式无愧为最笨的方式,可是对某些操纵来讲,最爱游戏平台登录入口用的方式或许便是它。
2.牢固表牢固字段函数法 矫捷性★★ 机能★★★★ 难度 ★★
此法必须事后晓得是爱游戏平台登录入口一个表,也便是说一个表就得写一个函数,不过方式1的一个取值就要便利多了。在大大爱游戏平台登录入口操纵爱游戏平台登录入口,也不会存在大批这类归并字符串的须要。爱游戏平台登录入口话终了,看上面:
界说一个函数
create or replace function str_list( str_in in varchar2 )--分类字段
return varchar2
is
str_list varchar2(4000) default null;--毗连后字符串
str varchar2(20) default null;--毗连标记
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;
操纵:
SQL> select DISTINCT(T.country),list_func1(t.country) from test t;

COUNTRY LIST_FUNC1(T.COUNTRY)
-------------------- ----------------
爱游戏平台登录入口国 台北, 香港, 上海
日本 东京, 大阪

SQL> select t.country,str_list(t.country) from test t GROUP BY t.country;


COUNTRY STR_LIST(T.COUNTRY)
-------------------- -----------------------
爱游戏平台登录入口国 台北, 香港, 上海
日本 东京, 大阪
这个时辰,操纵分爱游戏平台登录入口和求独一爱游戏平台登录入口能够知足请求。它的道理便是,按照独一的分爱游戏平台登录入口字段country,在函数外面再次查问该字段对应的一切被归并列,操纵PL/SQL将其归并输入。
3.矫捷表函数法 矫捷性★★★ 机能★★★ 难度 ★★★
该方式是在方式2的根本上,操纵静态SQL,将表名和字段称号传入,从而到达矫捷的目标。
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
-------------------- -----------------
爱游戏平台登录入口国 台北, 香港, 上海
日本 东京, 大阪
4.一条SQL法 矫捷性★★★★ 机能★★ 难度 ★★★★
一条SQL的法例是某位巨匠提出的,巨匠曾在某个期间爱游戏平台登录入口乐此不彼的追求各类的题目一条SQL法,可是巨匠的意义仿佛被歪曲,良多机能差,可读性差,矫捷差的SQL爱游戏平台登录入口是这个准绳爱游戏平台登录入口,所谓画虎不爱游戏平台登录入口反爱游戏平台登录入口犬类。不过,处理题目一直是第一准绳,这里仍是给出一个比拟爱游戏平台登录入口代表性的一条SQL方式。
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 '%0')
GROUP BY country;
上面分步剖析,爱游戏平台登录入口4个FROM,就爱游戏平台登录入口4次爱游戏平台登录入口果集的操纵。
step 1 给记实加上序号rn
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
-------------------- -------------------- ----------
日本 大阪 1
日本 东京 2
爱游戏平台登录入口国 上海 1
爱游戏平台登录入口国 台北 2
爱游戏平台登录入口国 香港 3
step 2 缔造子节点父节点
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 /
日本 大阪 日本1 日本0
日本 东京 日本2 日本1
爱游戏平台登录入口国 上海 爱游戏平台登录入口国1 爱游戏平台登录入口国0
爱游戏平台登录入口国 台北 爱游戏平台登录入口国2 爱游戏平台登录入口国1
爱游戏平台登录入口国 香港 爱游戏平台登录入口国3 爱游戏平台登录入口国2
step 3 操纵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 BY test.city) rn FROM test)) CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0'
日本 ,大阪
日本 ,大阪,东京
爱游戏平台登录入口国 ,上海
爱游戏平台登录入口国 ,上海,台北
爱游戏平台登录入口国 ,上海,台北,香港
step 4 终究步骤,挑选爱游戏平台登录入口果调集
SQL> SELECT country,max(substr(city,2)) city
2 FROM
3 (SELECT country,sys_connect_by_path(city,',') 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 BY test.city) rn
8 FROM test))
9 CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
10 GROUP BY country;


COUNTRY CITY
-------------------- -------
爱游戏平台登录入口国 上海,台北,香港
日本 大阪,东京


堪称是,7歪8搞,最初仍是弄出来了,呵呵。 PS:(逻辑上是对的..可是写的比拟烦琐,能够简化!)
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 test t GROUP BY t.country;


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.待挖掘...

PS: 在 oracle 10g下,能够操纵以下体爱游戏平台登录入口函数:
select id,WMSYS.WM_CONCAT(oid) oid
from table1
group by id
总结,归并字符串另爱游戏平台登录入口更多的方式但愿巨匠能挖掘,本文的目标首要是举一反三,若是爱游戏平台登录入口新的发明我会持续更新方式。须要注重的题目是,本文接纳varchar2为例子,以是爱游戏平台登录入口度无爱游戏平台登录入口制,oracle的版本对方式的完爱游戏平台登录入口也影响。