有如下表SRS_B_CW_TESTCity People Make广州 1 A广州 2 B广州 3 C上海 4 A上海 5 E广州 6 A上海 7 E
实现如下效果City People Make上海 16 AE广州 12 ABC
我们一步一步的学习
(1)如果直接group by则只能数字sum,字符串无法相加select City,sum(People) as People from SRS_B_CW_TEST
group by City order by City实现效果如下City People上海 16广州 12
(2)下面的写法错误,加不上Make列select City,sum(People) as People,Make from SRS_B_CW_TEST group
by City order by City
(3)如果写成下面的sql语句select City,sum(People) as People from SRS_B_CW_TEST group by City,Make
order by City实现效果如下City People上海 4上海 12广州 7广州 3广州 2
(4)这时就可以加上Make列select City,sum(People) as People,Make from SRS_B_CW_TEST group by City,Make
order by City实现效果如下City People Make上海 4 A上海 12 E广州 7 A广州 3 B广州 2 C
(5)下面想办法实现Make列的字符串相加
(6)构造树,sql语句如下select City,People,Make, row_number()over(order by City) RN,
row_number()over(partition by City order by City) RMfrom SRS_B_CW_TEST实现效果如下City People Make RN RM上海 4 A 1 1上海 5 E 2 2上海 7 E 3 3广州 6 A 4 1广州 3
C 5 2广州 2 B 6 3广州 1 A 7 4
(7)有了树,就可以使用树型函数SYS_CONNECT_BY_PATH
(8)但是!如果按照(6)构造的树拼字符串的话会出问题,先写sql语句select City,People,sys_connect_by_path(Make,',')
as Makefrom ( select City,People,Make, row_number()over(order by City) RN, row_number()over(partition by City order by City) RM from SRS_B_CW_TEST)start with RM=1connect by prior RN=RN-1实现效果如下City People Make上海
4 ,A上海 5 ,A,E上海 7 ,A,E,E广州 6 ,A,E,E,A广州 3 ,A,E,E,A,C广州 2 ,A,E,E,A,C,B广州 1 ,A,E,E,A,C,B,A广州 6 ,A只有7条数据,怎么多了1条,原来是RN没取好,不同组数字必须要'断开',导致connect by prior RN=RN-1处理时出了问题
(9)查找Oracle分析函数,我们发现一个函数对我们很有用rank(),于是我们修改6的sql语句select City,People,Make, rank()over(order
by City) + row_number()over(order by City) RN, row_number()over(partition by City order by City) RMfrom SRS_B_CW_TEST实现效果如下City People Make RN RM上海 4 A 2 1上海 5 E 3 2上海 7
E 4 3广州 6 A 8 1广州 3 C 9 2广州 2 B 10 3广州 1 A 11 4这样就'断开'了
(10)重写(8)的sql语句select City,People,sys_connect_by_path(Make,',') as Makefrom ( select
City,People,Make, rank()over(order by City) + row_number()over(order by City) RN, row_number()over(partition by City order by City) RM from SRS_B_CW_TEST)start with RM=1connect by prior RN=RN-1实现效果如下City People
Make上海 4 ,A上海 5 ,A,E上海 7 ,A,E,E广州 6 ,A广州 3 ,A,C广州 2 ,A,C,B广州 1 ,A,C,B,A
(11)目的快要实现了,下面用sum求和,max函数取出最大值select City,sum(People) as People,max(sys_connect_by_path(Make,','))
as Makefrom ( select City,People,Make, rank()over(order by City) + row_number()over(order by City) RN, row_number()over(partition by City order by City) RM from SRS_B_CW_TEST)start with RM=1connect by prior RN=RN-1group byCity实现效果如下City
People Make上海 16 ,A,E,E广州 12 ,A,C,B,A
(12)去处第一个逗号,用ltrim函数select City,sum(People) as People,ltrim(max(sys_connect_by_path(Make,',')),',')
as Makefrom ( select City,People,Make, rank()over(order by City) + row_number()over(order by City) RN, row_number()over(partition by City order by City) RM from SRS_B_CW_TEST)start with RM=1connect by prior RN=RN-1group byCity实现效果如下City
People Make上海 16 A,E,E广州 12 A,C,B,A
(13)最后,去除重复的英文字母,这里需要借助函数的力量
(14)自己写一个函数/************************************************************************************创建者：曾浩创建时间：2007-9-27最新修改者：曾浩最新修改时间：2007-9-27用途：改进的split函数,
实现这样的效果 输入字符串123,123,234,345,234,345,456和字符串, 输出123,234,345,456************************************************************************************/create or replace function ZH_SPLIT(v_string in varchar2, v_delimiter in varchar2) return varchar2is
j int:=0; i int:=1; len_string int:=0; len_delimiter int:=0; str varchar2(4000); v_return varchar2(4000);begin len_string := LENGTH(v_string); len_delimiter := LENGTH(v_delimiter); while j < len_string loop j := INSTR(v_string,
v_delimiter, i); if j = 0 then j := len_string; str := SUBSTR(v_string, i); if instr(v_return, str) > 0 then null; else v_return:=v_return||str||','; end if;
if i >= len_string then exit; end if; else str := SUBSTR(v_string, i, j - i); i := j + len_delimiter; if instr(v_return, str) > 0 then null; else v_return:=v_return||str||',';
end if; end if; end loop; v_return := substr(v_return, 1, length(v_return)-1); return v_return;end;
(15)调用这个函数select City,sum(People) as People,ZH_SPLIT(ltrim(max(sys_connect_by_path(Make,',')),','),',')
as Makefrom ( select City,People,Make, rank()over(order by City) + row_number()over(order by City) RN, row_number()over(partition by City order by City) RM from SRS_B_CW_TEST)start with RM=1connect by prior RN=RN-1group byCity实现效果如下City
People Make上海 16 A,E广州 12 A,C,B
(16)大功告成!