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)大功告成!