oracle不同行值相加,Oracle不同行的某列字符串相加,去除重復值

有如下表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

(16)大功告成!