with tb1 as( SELECT 2 A,3 B,5 C,'X' D FROM DUAL UNION ALL SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL SELECT 2 A,3 B,5 C,'Z' D FROM DUAL ) SELECT A,B,C,wm_concat(D) D FROM TB1 GROUP BY A,B,C;
select a,b,c,wm_concat(d)d from t group by a,b,c
oracle 10g 以上with tbl as ( select 1 as a, 2 as b, 3 as c, 'x' as d from dual union all select 1 as a, 2 as b, 3 as c, 'y' as d from dual union all select 1 as a, 2 as b, 3 as c, 'z' as d from dual union all select 2 as a, 2 as b, 3 as c, 'a' as d from dual union all select 2 as a, 2 as b, 3 as c, 'b' as d from dual union all select 2 as a, 2 as b, 3 as c, 'c' as d from dual ) select from tbl connect by select a, b, c, replace(wm_concat(d), ',', '') as d from tbl group by a, b, c;
Oracle 10g或以上版本才可以使用wm_concat函数
改了一点点with tb1 as( SELECT 2 A,3 B,5 C,'X' D FROM DUAL UNION ALL SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL SELECT 2 A,4 B,5 C,'Z' D FROM DUAL ) SELECT A,B,C,replace(wm_concat(D),',','') D FROM TB1 GROUP BY A,B,C;
select t.a,t.b,t.c,Wm_concat(t.d) from tab1 t where (t.a, t.b,t.c) in (select a, b,c from tab1 group by a, b,c having count(*) > 1) group by t.a,t.b,t.c
9i 试试下面的代码 with tbl as ( select 1 as a, 2 as b, 3 as c, 'x' as d from dual union all select 1 as a, 2 as b, 3 as c, 'y' as d from dual union all select 1 as a, 2 as b, 3 as c, 'z' as d from dual union all select 2 as a, 2 as b, 3 as c, 'a' as d from dual union all select 2 as a, 2 as b, 3 as c, 'b' as d from dual union all select 2 as a, 2 as b, 3 as c, 'c' as d from dual ) select a, b, c, ltrim(sys_connect_by_path(d, ','), ',') as d from (select a,b,c,d, row_number() over(partition by a,b,c order by rownum) as rn from tbl) t where connect_by_isleaf = 1 connect by prior a || b || c = a || b || c and prior rn = rn - 1 start with rn = 1;
connect_by_isleaf也是10g的,需要变通下~~ SQL> WITH t AS ( 2 SELECT '2' a,'3' b,'5' c,'X' d FROM DUAL UNION ALL 3 SELECT '2' a,'3' b,'5' c,'Y' d FROM DUAL UNION ALL 4 SELECT '2' a,'3' b,'5' c,'Z' d FROM DUAL UNION ALL 5 SELECT '2' a,'3' b,'6' c,'A' d FROM DUAL UNION ALL 6 SELECT '2' a,'3' b,'7' c,'B' d FROM DUAL UNION ALL 7 SELECT '2' a,'3' b,'7' c,'C' d FROM DUAL 8 ), 9 t1 AS ( 10 SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.a,t.b,t.c ORDER BY t.d) rn FROM t 11 ) 12 SELECT m.a, 13 m.b, 14 m.c, 15 MAX(m.d) KEEP(DENSE_RANK LAST ORDER BY m.rn) d 16 FROM ( 17 SELECT t1.a, 18 t1.b, 19 t1.c, 20 t1.rn, 21 SUBSTR(SYS_CONNECT_BY_PATH(t1.d, ','),2) d 22 FROM t1 23 START WITH t1.rn = 1 24 CONNECT BY PRIOR t1.rn = t1.rn - 1 25 AND PRIOR t1.a = t1.a 26 AND PRIOR t1.b = t1.b 27 AND PRIOR t1.c = t1.c 28 ) m 29 GROUP BY m.a,m.b,m.c 30 ;A B C D - - - -------------------------------------------------------------------------------- 2 3 5 X,Y,Z 2 3 6 A 2 3 7 B,C
那就自己写个函数吧,也很简单的 CREATE OR REPLACE FUNCTION fun_concat ( vi_a VARCHAR2, vi_b VARCHAR2, vi_c VARCHAR2 ) RETURN VARCHAR2 IS v_result VARCHAR2 (1000) := ''; BEGIN FOR c_d IN (SELECT d FROM t WHERE a = vi_a AND b = vi_b AND c = vi_c ORDER BY d) LOOP v_result := v_result || c_d.d; END LOOP; RETURN v_result; END; select a,b,c,fun_concat(a,b,c) d from ( select a,b,c from t group by a,b,c);
with tb1 as(
SELECT 2 A,3 B,5 C,'X' D FROM DUAL UNION ALL
SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL
SELECT 2 A,3 B,5 C,'Z' D FROM DUAL
)
SELECT A,B,C,wm_concat(D) D FROM TB1 GROUP BY A,B,C;
(
select 1 as a, 2 as b, 3 as c, 'x' as d from dual
union all
select 1 as a, 2 as b, 3 as c, 'y' as d from dual
union all
select 1 as a, 2 as b, 3 as c, 'z' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'a' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'b' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'c' as d from dual
)
select
from tbl
connect by
select a, b, c, replace(wm_concat(d), ',', '') as d from tbl
group by a, b, c;
SELECT 2 A,3 B,5 C,'X' D FROM DUAL UNION ALL
SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL
SELECT 2 A,3 B,5 C,'Y' D FROM DUAL UNION ALL
SELECT 2 A,4 B,5 C,'Z' D FROM DUAL
)
SELECT A,B,C,replace(wm_concat(D),',','') D FROM TB1 GROUP BY A,B,C;
from tab1 t
where (t.a, t.b,t.c) in
(select a, b,c
from tab1
group by a, b,c
having count(*) > 1) group by t.a,t.b,t.c
with tbl as
(
select 1 as a, 2 as b, 3 as c, 'x' as d from dual
union all
select 1 as a, 2 as b, 3 as c, 'y' as d from dual
union all
select 1 as a, 2 as b, 3 as c, 'z' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'a' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'b' as d from dual
union all
select 2 as a, 2 as b, 3 as c, 'c' as d from dual
)
select a, b, c, ltrim(sys_connect_by_path(d, ','), ',') as d
from (select a,b,c,d, row_number() over(partition by a,b,c order by rownum) as rn
from tbl) t
where connect_by_isleaf = 1
connect by prior a || b || c = a || b || c and prior rn = rn - 1
start with rn = 1;
SQL> WITH t AS (
2 SELECT '2' a,'3' b,'5' c,'X' d FROM DUAL UNION ALL
3 SELECT '2' a,'3' b,'5' c,'Y' d FROM DUAL UNION ALL
4 SELECT '2' a,'3' b,'5' c,'Z' d FROM DUAL UNION ALL
5 SELECT '2' a,'3' b,'6' c,'A' d FROM DUAL UNION ALL
6 SELECT '2' a,'3' b,'7' c,'B' d FROM DUAL UNION ALL
7 SELECT '2' a,'3' b,'7' c,'C' d FROM DUAL
8 ),
9 t1 AS (
10 SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.a,t.b,t.c ORDER BY t.d) rn FROM t
11 )
12 SELECT m.a,
13 m.b,
14 m.c,
15 MAX(m.d) KEEP(DENSE_RANK LAST ORDER BY m.rn) d
16 FROM (
17 SELECT t1.a,
18 t1.b,
19 t1.c,
20 t1.rn,
21 SUBSTR(SYS_CONNECT_BY_PATH(t1.d, ','),2) d
22 FROM t1
23 START WITH t1.rn = 1
24 CONNECT BY PRIOR t1.rn = t1.rn - 1
25 AND PRIOR t1.a = t1.a
26 AND PRIOR t1.b = t1.b
27 AND PRIOR t1.c = t1.c
28 ) m
29 GROUP BY m.a,m.b,m.c
30 ;A B C D
- - - --------------------------------------------------------------------------------
2 3 5 X,Y,Z
2 3 6 A
2 3 7 B,C
CREATE OR REPLACE FUNCTION fun_concat (
vi_a VARCHAR2,
vi_b VARCHAR2,
vi_c VARCHAR2
)
RETURN VARCHAR2
IS
v_result VARCHAR2 (1000) := '';
BEGIN
FOR c_d IN (SELECT d
FROM t
WHERE a = vi_a AND b = vi_b AND c = vi_c
ORDER BY d)
LOOP
v_result := v_result || c_d.d;
END LOOP; RETURN v_result;
END;
select a,b,c,fun_concat(a,b,c) d from (
select a,b,c from t group by a,b,c);