oracle请帮忙看看这么个需求sql语句该怎么写

Question

[[email protected]][/email]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 staffcode
----------------------------------------
1 小李,小张
2 小李
3 小张,小王

Answer 1

建议你单独写一个函数，将tab1表中的staffcode转换为staffname.比如说
create or replace function CONVERT_NAME(staffcode varchar2) return varchar2;
然后再查询时调用函数即可
select id,convert_name(staffcode) from tab1 where active='1'

Answer 2

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 distinct id,SubStr(staffcode,LEVEL,1) staffcode,active 
FROM tab1 WHERE SubStr(staffcode,LEVEL,1) !=Chr(44) CONNECT BY LEVEL<=Length(staffcode)
) 
SELECT t1.id,wm_concat(t2.staffname) staffcode FROM tmp t1,tab2 t2
WHERE t1.staffcode=t2.staffcode AND t1.active=1
GROUP BY t1.idID   STAFFCODE
---------------------
1    小李,小张
2    小李     
3    小张,小王

Answer 3

select id, wm_concat(staffname) staffcode
from (select a.id,b.staffname from tab1 a,tab2 b where a.id=b.id ande a.staffcode like '%b.staffcode%' and active=1 )
group by id;

Answer 4

--还有个简单的方法，用instr函数作条件：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
)
SELECT t1.id,wm_concat(t2.staffname) staffcode FROM tab1 t1,tab2 t2
WHERE InStr(t1.staffcode,t2.staffcode)>0 AND t1.active=1 
GROUP BY t1.id结果：
ID   STAFFCODE
---------------------
1    小李,小张
2    小李     
3    小张,小王

Answer 5

上面的sql跟记录数无关,是可以用的.

Answer 6

只是tab1这么存记录很怪异.web界面拼的串就不能分解成记录?非得按传过来的值存储?这样的存储方式降低了查询效率

Answer 7

gelyon在4楼发布的方法是正解，支持数据记录不确定的情况。需要补充一点的是instr函数需要改造一下，以免发生instr('AC,AE','E') > 0的情况，建议改成InStr(','||t1.staffcode||',',','||t2.staffcode||',')

Answer 8

SQL> with tb1 AS(
  2  SELECT 1 id,'A,B'staffcode,1 active FROM dual UNION ALL
  3  SELECT 2, 'A', 1  FROM dual UNION ALL
  4  SELECT 3, 'B,C', 1 FROM dual UNION ALL
  5  SELECT 4, 'C', 0  FROM dual
  6  )
  7  ,
  8  tb2 as
  9  (select distinct id,staffcode,active
 10  from (select id,active,
 11  substr(','||staffcode||',',instr(','||staffcode||',',',',1,level)+1,1) staffcode
 12  from tb1
 13  connect by level<=length(','||staffcode||',') and connect_by_root(id)=id)
 14  where regexp_like(staffcode,'[[:alpha:]]')),
 15  tb3 AS(
 16  SELECT 1 id, 'A' staffcode, '小李' staffname FROM dual UNION ALL
 17  SELECT 2, 'B', '小张'  FROM dual UNION ALL
 18  SELECT 3, 'C', '小王' FROM dual
 19  )
 20  select a.id,wm_concat(b.staffname) staffname
 21  from tb2 a,tb3 b
 22  where a.staffcode=b.staffcode and a.active=1
 23  group by a.id
 24  /        ID STAFFNAME
---------- ------------------------------
         1 小李,小张
         2 小李
         3 小张,小王

Answer 9

感谢各位朋友的帮助！
尤其感谢4楼的gelyon
不过，我用的oracle版本较低，系统里没有wm_concat函数
请问该怎么处理？

Answer 10

你可以参考我的blog: http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspx

Answer 11

那就写个函数 自己循环拼接下就可以了 跟wm_concat一样的

Answer 12

--简单点的话，写个函数实现10g中wm_concat酒行了，我blog里有类似的，你照着写个就可以了：
--这里，暂时给你写了个sys_connect_by_path的 版本要求9i以及9i以前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 13

--应你的要求，给你写了个在自定义函数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     小张,小王

Answer 14

兄弟 能修改表结构吗 如果表结构 能修改 问题 就简单了 
TAB1表
id staffcode active
--------------------------------------------------------------
1 A,B 1
2 A 1
3 B,C 1
4 C 0
…… …… ……
可以改成
TAB1表
id staffcode active
--------------------------------------------------------------
1 ‘A’,‘B’ 1
2 ‘A’ 1
3 ‘B’,‘C’ 1
4 ‘C’ 0
…… …… ……
这样 就简单化了