Oracle中分组后拼接分组字符串.docx

Oracle中分组后拼接分组字符串先分组，再把分组后的属于某组的多条记录的某字段进行拼接。实现方式如下：/*?--创建表?test*/create?table?test(NO?NUMBER,VALUE?VARCHAR2(100),NAME?VARCHAR2(100));/*?----插入数据*/insert?into?testselect?*?from(select?1,a,测试1?from?dual?union?allselect?1,b,测试2?from?dual?union?allselect?1,c,测试3?from?dual?union?allselect?1,d,测试4?from?dual?union?allselect?2,e,测试5?from?dual?union?allselect?4,f,测试6?from?dual?union?allselect?4,g,测试7?from?dual);/*--Sql语句：*/select?No,ltrim(max(sys_connect_by_path(Value,?;)),?;)?as?Value,ltrim(max(sys_connect_by_path(Name,?;)),?;)?as?Namefrom?(select?No,Value,Name,rnFirst,lead(rnFirst)?over(partition?by?No?order?by?rnFirst)?rnNextfrom?(select?a.No,a.Value,a.Name,row_number()?over(order?by?a.No,?a.Value?desc)?rnFirstfrom?Test?a)?tmpTable1)?tmpTable2start?with?rnNext?is?nullconnect?by?rnNext?=?prior?rnFirstgroup?by?No;/*--检索结果如下：*//*NO?VALUE?NAME1?a;b;c;d?测试1;测试2;测试3;测试42?e?测试54?f;g?测试6;测试7*//*********************************?分析?*************************************/--简单解释一下那个Sql吧：/*--1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：*/select?a.No,a.Value,a.Name,row_number()?over(order?by?a.No,?a.Value?desc)?rnFirstfrom?Test?a;/*该语句结果如下：NO?VALUE?NAME?RNFIRST1?d?测试4?11?c?测试3?21?b?测试2?31?a?测试1?42?e?测试5?54?g?测试7?64?f?测试6?7*//*--2、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：*/select?No,Value,Name,rnFirst,lead(rnFirst)?over(partition?by?No?order?by?rnFirst)?rnNext/*--lead(rnFirst)：取得下一行记录的rnFirst字段--over(partition?by?No?order?by?rnFirst)?按rnFirst排序，并按No分区，--分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示*/from?(select?a.No,a.Value,a.Name,row_number()?over(order?by?a.No,?a.Value?desc)?rnFirstfrom?Test?a)?tmpTable1;/*--该语句结果如下：NO?VALUE?NAME?RNFIRST?RNNEXT1?d?测试4?1?21?c?测试3?2?31?b?测试2?3?41?a?测试1?4?NULL2?e?测试5?5?NULL4?g?测试7?6?74?f?测试6?7?NULL*//*--3、最后就是最外层的sys_connect_by_path函数与start递归了*/sys_connect_by_path(Value,?;)start?with?rnNext?is?nullconnect?by?rnNext?=?prior?rnFirst/*--这个大概意思就是从rnNext为null的那条记录开始，递归查找，--如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来，--大家可以先试试下面这个没有Max和Group的Sql：*/select?No