跪求sql

[smallcrocodile]

2个表T1,T2

T1表中有2个字段，id，name，name是id对应中文名；
T2表中有一个字段group，group是不若干id值的组合，用“；”隔开，查询时如何让group以对应name名显示？？

[秋雨飘落]

写函数，转换

[horizonlyhw]

with a as(
select 'a' id,'aa' name from dual
union all
select 'b' id,'bb' name from dual
union all
select 'c' id,'cc' name from dual
union all
select 'd' id,'dd' name from dual
)
,b as (
select 'a,b,c' group1 from dual
union all
select 'c,d' group1 from dual
)

select b.group1, wm_concat(decode(instr(b.group1,a.id),0,'',a.name))
from a,b
group by  b.group1

[秋雨飘落]

[Quote=引用 2 楼 horizonlyhw 的回复:]
SQL code

with a as(
select 'a' id,'aa' name from dual
union all
select 'b' id,'bb' name from dual
union all
select 'c' id,'cc' name from dual
union all
select 'd' id,'dd' name from dual
)
……
[/Quote]

---------我也觉得很好，不过最后面是GROUP,

create or replace function fun_JDZD_DPZL(shuru VARCHAR2) return VARCHAR2 IS

  zhi1 varchar2(1000);
  zhi2 varchar2(1000);
  zhi3 varchar2(1000);
  zhi4 varchar2(1000);
  shuchu  varchar2(1000);
BEGIN
  select substr(shuru,instr(shuru,'/',1,1)+1,instr(shuru,'/',1,2)-instr(shuru,'/',1,1)-1),
        substr(shuru,instr(shuru,'/',1,2)+1,instr(shuru,'/',1,3)-instr(shuru,'/',1,2)-1),
       substr(shuru,instr(shuru,'/',1,3)+1,instr(shuru,'/',1,4)-instr(shuru,'/',1,3)-1),
       substr(shuru,instr(shuru,'/',1,4)+1,instr(shuru,'/',1,5)-instr(shuru,'/',1,4)-1)
    into zhi1, zhi2, zhi3, zhi4
    from dual ;
select wmsys.wm_concat(mc) into shuchu from  T_JDZD_RY_ZD_DPZL where xh in (zhi1, zhi2, zhi3, zhi4);
  return shuchu;
END;

[smallcrocodile]

好像执行有问题

[jinglingqaz]

学习了！

[yelang]

select name,group from t1 right join t2  ON  InStr(t2.group,t1.id)>0 

[toadzw]

最好重新设计T2表，T2表设计就有问题啊，应该重新整理二个表的关系；不要用;来作相关的分割符来组合一个GROUP，应该是一个一对多的关系，如果你想这样设计的话可以把T1表中增加一个GROUP ID

[BenChiM888]

--9i 10g 通用的方法
[SCOTT@myorcl] SQL>WITH T1 AS(
  2    SELECT 'GROUP001' ID, '张三' NAME FROM dual UNION ALL
  3    SELECT 'GROUP002' ID, '李四' NAME FROM dual UNION ALL
  4    SELECT 'GROUP003' ID, '王五' NAME FROM dual UNION ALL
  5    SELECT 'GROUP004' ID, '赵六' NAME FROM dual
  6  ),T2 AS(
  7    SELECT 'GROUP001;GROUP002' GROUPID FROM dual UNION ALL
  8    SELECT 'GROUP003;GROUP004' GROUPID FROM dual
  9  ),T3 AS(
 10    SELECT T2.GROUPID,T1.NAME,ROW_NUMBER()OVER(PARTITION BY T2.GROUPID ORDER BY 1) ROW_
 11      FROM T1,T2
 12     WHERE INSTR(T2.GROUPID,T1.ID) > 0
 13  ),T4 AS(
 14    SELECT T3.GROUPID,
 15          LTRIM(SYS_CONNECT_BY_PATH(T3.NAME,';'),';') NAME ,
 16          LEVEL LEV
 17     FROM T3
 18    CONNECT BY PRIOR ROW_ = ROW_ -1 AND PRIOR GROUPID = GROUPID
 19  )SELECT T4.GROUPID,MAX(T4.NAME)KEEP(DENSE_RANK FIRST ORDER BY LEV DESC) NAME
 20      FROM T4
 21     GROUP BY GROUPID;

GROUPID           NAME
----------------- --------------------
GROUP001;GROUP002 张三;李四
GROUP003;GROUP004 王五;赵六


--10G可以用下面的方法
[SCOTT@myorcl] SQL>WITH T1 AS(
  2    SELECT 'GROUP001' ID, '张三' NAME FROM dual UNION ALL
  3    SELECT 'GROUP002' ID, '李四' NAME FROM dual UNION ALL
  4    SELECT 'GROUP003' ID, '王五' NAME FROM dual UNION ALL
  5    SELECT 'GROUP004' ID, '赵六' NAME FROM dual
  6  ),T2 AS(
  7    SELECT 'GROUP001;GROUP002' GROUPID FROM dual UNION ALL
  8    SELECT 'GROUP003;GROUP004' GROUPID FROM dual
  9  )SELECT T2.GROUPID,REPLACE(WM_CONCAT(T1.NAME),',',';') AS NAME
 10     FROM T1,T2
 11    WHERE INSTR(T2.GROUPID,T1.ID) > 0
 12    GROUP BY T2.GROUPID;

GROUPID           NAME
----------------- --------------------
GROUP001;GROUP002 张三;李四
GROUP003;GROUP004 王五;赵六