oracle多行記錄合并連接聚合字符串的幾種方法

《oracle多行記錄合并連接聚合字符串的幾種方法》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、Oracle多行記錄合并/連接/聚合字符串的幾種方法[轉(zhuǎn)]2008-09-1311:32怎么合并多行記錄的字符串，一直是oracle新手喜歡問的SQL問題之一，關(guān)于這個問題的帖子我看過不下30個了，現(xiàn)在就對這個問題，進行一個總結(jié)。什么是合并多行字符串（連接字符串）呢，例如：SQL>desctest;NameTypeNullableDefaultComments------------------------------------------COUNTRYVARCHAR2(20)YCITYVARCHAR2(20)YSQL>select*fro

2、mtest;COUNTRYCITY----------------------------------------中國臺北中國香港中國上海日本東京日本大阪要求得到如下結(jié)果集：---------------------------中國臺北,香港,上海日本東京，大阪實際就是對字符實現(xiàn)一個聚合功能，我很奇怪為什么Oracle沒有提供官方的聚合函數(shù)來實現(xiàn)它呢：）下面就對幾種經(jīng)常提及的解決方案進行分析（有一個評測標準最高★★★★★）：1.被集合字段范圍小且固定型靈活性★性能★★★★難度★這種方法的原理在于你已經(jīng)知道CITY字段的值有幾種，且還不算太多，

3、如果太多這個SQL就會相當?shù)拈L。?？蠢樱篠QL>selectt.country,2MAX(decode(t.city,'臺北',t.city

4、

5、',',NULL))

6、

7、3MAX(decode(t.city,'香港',t.city

8、

9、',',NULL))

10、

11、4MAX(decode(t.city,'上海',t.city

12、

13、',',NULL))

14、

15、5MAX(decode(t.city,'東京',t.city

16、

17、',',NULL))

18、

19、6MAX(decode(t.city,'大阪',t.city

20、

21、',',NULL))7fromtesttGROUP

22、BYt.country8/COUNTRYMAX(DECODE(T.CITY,'臺北',T.CIT--------------------------------------------------中國臺北,香港,上海,日本東京,大阪,大家一看，估計就明白了（如果不明白，好好補習MAXDECODE和分組）。這種方法無愧為最笨的方法，但是對某些應(yīng)用來說，最有效的方法也許就是它。2.固定表固定字段函數(shù)法靈活性★★性能★★★★難度★★此法必須預(yù)先知道是哪個表，也就是說一個表就得寫一個函數(shù)，不過方法1的一個取值就要便捷多了。在大多數(shù)應(yīng)用中，也不會存在大

23、量這種合并字符串的需求。廢話完畢，看下面：定義一個函數(shù)createorreplacefunctionstr_list(str_ininvarchar2)--分類字段returnvarchar2isstr_listvarchar2(4000)defaultnull;--連接后字符串strvarchar2(20)defaultnull;--連接符號beginforxin(selectTEST.CITYfromTESTwhereTEST.COUNTRY=str_in)loopstr_list:=str_list

24、

25、str

26、

27、to_char(x.ci

28、ty);str:=',';endloop;returnstr_list;end;使用：SQL>selectDISTINCT(T.country),list_func1(t.country)fromtestt;COUNTRYLIST_FUNC1(T.COUNTRY)------------------------------------中國臺北,香港,上海日本東京,大阪SQL>selectt.country,str_list(t.country)fromtesttGROUPBYt.country;COUNTRYSTR_LIST(T.COUNTR

29、Y)-------------------------------------------中國臺北,香港,上海日本東京,大阪這個時候，使用分組和求唯一都可以滿足要求。它的原理就是，根據(jù)唯一的分組字段country，在函數(shù)里面再次查詢該字段對應(yīng)的所有被合并列，使用PL/SQL將其合并輸出。3.靈活表函數(shù)法靈活性★★★性能★★★難度★★★該方法是在方法2的基礎(chǔ)上，使用動態(tài)SQL，將表名和字段名稱傳入，從而達到靈活的目的。createorreplacefunctionstr_list2(key_nameinvarchar2,keyinvarchar

30、2,conameinvarchar2,tnameinvarchar2)returnvarchar2astypercisrefcursor;strvarchar2(4