这样的sql语句该怎么写？

Question

TAB1表
id staffcode active
--------------------------------------------------------------
1 A,B 1
2 A   1
3 B,C 1
4 C   0
…… …… ……TAB2表
id staffcode staffname
--------------------------------------------------------------
1 A 小李
2 B 小张
3 C 小王
…… …… ……需求：统计TAB1表中，active=1的记录，对应的staffname
如下格式：
RESULT：
id staffname
----------------------------------------
1 小李,小张
2 小李 
3 小张,小王
说明：用的oracle版本比较低，没有wm_concat函数，请问该怎么处理?

Answer 1

SELECT t.id id, MAX(substr(sys_connect_by_path(t.staffname, ','), 2)) str
  FROM (SELECT id,
               staffname,
               row_number() over(PARTITION BY id ORDER BY staffname) rn
          FROM a) t
 START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id
 GROUP BY t.id;

Answer 2

create 一个function。
对每个tab1的staffcode，遍历tab2，得到需要的结果

Answer 3

--简单点的话，写个函数实现10g中wm_concat酒行了，我blog里有类似的，你照着写个就可以了：
--这里，暂时给你写了个sys_connect_by_path的 版本要求9i以及9i以前
--另外补充点，1楼不能用max取值，因为"小李，小王"，“小王”这两个取MAX的结果是“小王”，而不是“小李，小王”WITH tab1 AS(
SELECT 1 id,'A,B'staffcode,1 active FROM dual UNION ALL 
SELECT 2, 'A', 1  FROM dual UNION ALL 
SELECT 3, 'B,C', 1 FROM dual UNION ALL 
SELECT 4, 'C', 0  FROM dual
),
tab2 AS(
SELECT 1 id, 'A' staffcode, '小李' staffname FROM dual UNION ALL 
SELECT 2, 'B', '小张'  FROM dual UNION ALL 
SELECT 3, 'C', '小王' FROM dual
),
tmp AS(
    SELECT id,staffcode,active,Row_Number()over(PARTITION BY id ORDER BY id) rn
    FROM(
        SELECT distinct id,SubStr(staffcode,LEVEL,1) staffcode,active 
        FROM tab1 
        WHERE SubStr(staffcode,LEVEL,1) !=Chr(44) 
        CONNECT BY LEVEL<=Length(staffcode)
    )
) 
SELECT id, staffcode FROM (
    SELECT id, staffcode,Row_Number()over(PARTITION BY id ORDER BY Length(staffcode)DESC ) len 
    FROM(
        SELECT t1.id,LTrim(sys_connect_by_path(t2.staffname,','),',') staffcode
        FROM tmp t1,tab2 t2
        WHERE t1.staffcode=t2.staffcode AND t1.active=1 
        CONNECT BY PRIOR t1.id=t1.id AND PRIOR rn=rn-1
    )
)
WHERE len =1ID   STAFFCODE
------------------------
1    小李,小张
2    小李     
3    小张,小王

Answer 4

建个视图 create view  v_tb as
select a.id,b.staffname staffname
from tb1 a,tb2 b
where instr(a.staffcode,b.staffcode)>0
然后建个函数遍历接受就可以
create or replace function fun_name(v_id number) return varchar2
as
v_name varchar2(1000):='';
begin
for i in(select staffname from v_tb where id=v_id) loop
v_name:=v_name||','||i.staffname;
end loop;
return substr(v_name,2);
end;
select distinct id,fun_name(id) from v_tb

Answer 5

with t1 as (
select 1 id , 'A,B' staffcode, 1 active from dual
union all
select 2 id , 'A' staffcode, 1 active from dual
union all
select 3 id , 'B,C' staffcode, 1 active from dual
union all
select 4 id , 'A' staffcode, 0 active from dual
),
t2 as (
select 1 id , 'A' staffcode, '小张' staffname from dual
union all
select 2 id , 'B' staffcode, '小李' staffname from dual
union all
select 3 id , 'C' staffcode, '小王' staffname from dual 
)              
SELECT t.id id, MAX(substr(sys_connect_by_path(t.staffname, ','), 2)) str
  FROM (SELECT t1.id,
               t1.staffcode,
               t2.staffname,
               row_number() over(PARTITION BY t1.id ORDER BY instr(t1.staffcode,t2.staffcode)) rn
          FROM t1,t2
          where t1.active = 1
            and instr(t1.staffcode,t2.staffcode) > 0  ) t
 START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id
 GROUP BY t.id;

Answer 6

[code=SQL]
--应你的要求，给你写了个在自定义函数Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原数据:
SQL> select * from tab1;        ID STAFFC     ACTIVE
---------- ------ ----------
         1 A,B             1
         2 A               1
         3 B,C             1
         4 C               0SQL> select * from tab2;        ID ST STAFFNAME
---------- -- ------------
         1 A  小李
         2 B  小张
         3 C  小王SQL> --自定义函数:
SQL> CREATE OR REPLACE FUNCTION my_concat(str IN VARCHAR2 )
  2  RETURN VARCHAR2
  3  IS
  4  result VARCHAR2(4000);
  5  BEGIN
  6     FOR i IN (SELECT staffname FROM tab2 WHERE  InStr(str,staffcode)>0) LOOP
  7        result:=result||i.staffname||',';
  8     END LOOP;
  9     RETURN RTrim(result,',');
 10  END;
 11  /Function created.SQL> --测试：
SQL> SELECT id ,my_concat(staffcode) staffcode FROM tab1 WHERE active=1;
      
      ID    STAFFCODE
    -----------------------
      1     小李,小张
      2     小李     
      3     小张,小王[/code]