各位大虾看看如下SQL怎么写？

Question

A
----------------
id
6808676495
......B
----------------
id           conf
6808676495 1966876
6808676495 1967007
6808676495 1971919
6808676495 1971937
6808676495 1974749
6808676495 5000626
......其中 id 字段在 表 a 中是主键。在表 b 中记录条数不限。
有没有办法一条SQL 将结果 成如下？C
--------------------
id           conf_new
6808676495   1966876:1967007:1971919:1971937:1974749:5000626
......其中表 C 中 记录数保持 与表 A 不变.各位帮帮忙，多谢了！

Answer 1

可写一个函数，对于每一个id 返回一个conf的数组，再按照这种格式展示

Answer 2

[code=SQ]L-- 可以的，你可以查查wm_sys.wm_concat的用法，站内也有很多这类的帖子[/code]

Answer 3

select id,replace(conf,',',':') from 
(
select id,wm_sys.wm_concat(conf) as conf from tab
group by id
)

Answer 4

select a.id,replace(wm_concat(b.conf),',',':') conf_new
from a,b
where a.id=b.id
group by a.id

Answer 5

--还需要再排序一下，楼主要排序的效果吗？
with tab as
(
select '6808676495' id from dual
),
tab2 as
(
select '6808676495' id, '1966876' conf from dual union all
select '6808676495', '1967007' from dual union all
select '6808676495', '1967567' from dual union all
select '6808676495', '1963407' from dual union all
select '6808676495', '1960045' from dual
)
SELECT id, MAX(conf) conf
  FROM (SELECT t.id,
               wmsys.wm_concat(t.conf) over(PARTITION BY t.id ORDER BY t.conf) conf
          FROM (select tab.id,tab2.conf from tab,tab2 where tab.id = tab2.id) t)
 GROUP BY id;---------------------------
id                conf
6808676495 1960045,1963407,1966876,1967007,1967567

Answer 6

http://liuye066.javaeye.com/blog/649672

Answer 7

oracle10g以后使用wm_sys.wm_concat函数，
oracle9i和9i之前使用sys_connect_by_path函数SQL> with tmp as
  2  (
  3  select 6808676495 id, 1966876 conf from dual
  4  union all
  5  select 6808676495 id, 1967007 conf from dual
  6  union all
  7  select 6808676495 id, 1971919 conf from dual
  8  union all
  9  select 6808676495 id, 1971937 conf from dual
 10  union all
 11  select 6808676495 id, 1974749 conf from dual
 12  union all
 13  select 6808676495 id, 5000626 conf from dual
 14  )
 15  select id,max(substr(sys_connect_by_path(conf,':'),2)) confs
 16   from(select id,conf,rownum rn
 17          from tmp
 18        )
 19  start with rn = 1
 20  connect by rn = rownum
 21  group by id;
 
        ID CONFS
---------- --------------------------------------------------------------------------6808676495 1966876:1967007:1971919:1971937:1974749:5000626

Answer 8

再格式化下。SQL> with tmp as
  2  (
  3  select 6808676495 id, 1966876 conf from dual
  4  union all
  5  select 6808676495 id, 1967007 conf from dual
  6  union all
  7  select 6808676495 id, 1971919 conf from dual
  8  union all
  9  select 6808676495 id, 1971937 conf from dual
 10  union all
 11  select 6808676495 id, 1974749 conf from dual
 12  union all
 13  select 6808676495 id, 5000626 conf from dual
 14  )
 15  select id,max(substr(sys_connect_by_path(conf,':'),2)) confs
 16   from(select id,conf,rownum rn
 17          from tmp
 18        )
 19  start with rn = 1
 20  connect by rn = rownum
 21  group by id;
 
        ID CONFS
---------- --------------------------------------------------------------------------------
6808676495 1966876:1967007:1971919:1971937:1974749:5000626

Answer 9

create or replace procedure abtest(in_id in varchar2,
                                   o_cur OUT SYS_REFCURSOR) isbegin
  declare
  
    t_conf   varchar2(500);
    tb_count INT;
    STR      VARCHAR2(200);
  
    CURSOR mycur is
      select id, conf from b where id = in_id;
  
  begin
    t_conf := '';
  
    --先判断全局临时表是否存在，没存在则重新建立：
    select count(*)
      into tb_count
      from user_tables
     where table_name = 'REPROTTEST';
    if tb_count = 0 then
      STR := ' CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
           ID varchar2(10),
           new_conf VARCHAR2(500)
    ) ON COMMIT PRESERVE ROWS';
      execute immediate STR;
    end if;
  
    for cur in mycur loop
      if t_conf <> ' ' then
        t_conf := t_conf || ':' || cur.conf;
      else
        t_conf := t_conf || cur.conf;
      end if;
    end loop;
  
    --dbms_output.put_line(in_id || ' ' || t_conf);
  
    STR := 'insert into REPROTTEST(id,new_conf) values(''' || in_id || ''',''' || t_conf || ''')';    execute immediate STR;
    COMMIT;
    STR := 'SELECT * FROM REPROTTEST';
    OPEN o_cur FOR STR; -- 给游标变量赋值
  
  end;
end abtest;

Answer 10

返回的是列表
if         new_conf
---------- --------------------------------------------------------------------------------
6808676495 1966876:1967007:1971919:1971937:1974749:5000626

Answer 11

create or replace procedure abtest(in_id in varchar2,
                                   o_cur OUT SYS_REFCURSOR) isbegin
  declare
  
    t_conf   varchar2(500);
    tb_count INT;
    STR      VARCHAR2(200);
  
    CURSOR mycur is
      select id, conf from b where id = in_id;
  
  begin
    t_conf := '';
  
    --先判断全局临时表是否存在，没存在则重新建立：
    select count(*)
      into tb_count
      from user_tables
     where table_name = 'REPROTTEST';
    if tb_count = 0 then
      STR := ' CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
           ID varchar2(10),
           new_conf VARCHAR2(500)
    ) ON COMMIT PRESERVE ROWS';
      execute immediate STR;
    end if;
  
    for cur in mycur loop
      if t_conf <> ' ' then
        t_conf := t_conf || ':' || cur.conf;
      else
        t_conf := t_conf || cur.conf;
      end if;
    end loop;
  
    --dbms_output.put_line(in_id || ' ' || t_conf);
  
    STR := 'insert into REPROTTEST(id,new_conf) values(''' || in_id ||
           ''',''' || t_conf || ''')';
  
    execute immediate STR;
    COMMIT;
    STR := 'SELECT * FROM REPROTTEST';
    OPEN o_cur FOR STR; -- 给游标变量赋值
  
  end;
end abtest;
也可用其他语言调用，返回的是一个列表调用查看create or replace procedure p_test is
begin
  declare
    v_ID   varchar2(10);
    v_conf VARCHAR2(500);
  
    --定义游标:
    v_account_cur SYS_REFCURSOR;
    cur           SYS_REFCURSOR;
  
  begin
    --调用存储过程：
    abtest('6808676495', v_account_cur);
  
    fetch v_account_cur
      into v_ID, v_conf;
    --用循环显示游标中的记录：
    while v_account_cur%found loop
      dbms_output.put_line('The value of column ID is: ' || v_ID); --打引列ID
      dbms_output.put_line('The value of column new_conf is: ' || v_conf); --打引列new_conf         
      fetch v_account_cur
        into v_ID, v_conf;
    end loop;
    close v_account_cur;
    execute immediate ' truncate TABLE REPROTTEST ';
  exception
    when others then
      dbms_output.put_line(sqlerrm);
    
  end;
end p_test;