这样的sql语句该怎么写？

[fsolsh]

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函数，请问该怎么处理?

[YY_MM_DD]

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;

[47522341]

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

[gelyon]

--简单点的话，写个函数实现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 =1

ID   STAFFCODE
------------------------
1    小李,小张
2    小李     
3    小张,小王

[心中的彩虹]

建个视图

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

[47522341]

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;

[lnuwhy]

[Quote=引用 3 楼 gelyon 的回复:]
SQL code

--简单点的话，写个函数实现10g中wm_concat酒行了，我blog里有类似的，你照着写个就可以了：
--这里，暂时给你写了个sys_connect_by_path的 版本要求9i以及9i以前
--另外补充点，1楼不能用max取值，因为"小李，小王"，“小王”这两个取MAX的结果是“小王”，而不是“小李，小王”

WITH tab1 AS(
SELECT 1 ……
[/Quote]
确实，你这个需求不用wm函数，就要自己写个同样功能的函数

[gelyon]

[code=SQL]
--应你的要求，给你写了个在自定义函数

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production

SQL> --原数据:
SQL> select * from tab1;

        ID STAFFC     ACTIVE
---------- ------ ----------
         1 A,B             1
         2 A               1
         3 B,C             1
         4 C               0

SQL> 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]