有如下表SRS_B_CW_TEST
City 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) RM
from 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 Make
from (
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=1
connect 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) RM
from 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 Make
from (
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=1
connect 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 Make
from (
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=1
connect by prior RN=RN-1
group by
City
实现效果如下
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 Make
from (
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=1
connect by prior RN=RN-1
group by
City
实现效果如下
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 varchar2
is
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 Make
from (
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=1
connect by prior RN=RN-1
group by
City
实现效果如下
City People Make
上海 16 A,E
广州 12 A,C,B
