create table a(id varchar2(4),name varchar2(10));
create table b(id varchar2(4),manager varchar2(100));
表a: 表b:
----------------- -----------------
| id | name | | id | manager|
----------------- -----------------
| 1 | aaaa | | 1 | 1,2 |
----------------- -----------------
| 2 | bbbb |
-----------------
表b中manager字段是由表a中id字段拼接的,用逗号分开
现在想查出的结果为:
-----------------------
| id | manager |
-----------------------
| 1 | aaaa,bbbb |
-----------------------
请高手指教,谢谢……
-------------
| id | name |
-------------
| 1 | aaaa |
-------------
| 2 | bbbb |
-------------
表b:
----------------
| id | manager |
----------------
| 1 | 1,2 |
----------------
select b.id,wm_concat(a.name) name
from tba a,tbb b where instr(b.manager,a.id)>0
group by b.id
2 /ID NAME
---- ----------
1 aaaa
2 bbbbSQL> select * from tbb
2 /ID
----
MANAGER
--------------------------------------------------------------------
1
1,2
SQL> select b.id,wm_concat(a.name)
2 from tba a,tbb b where instr(b.manager,a.id)>0
3 group by b.id
4 /ID
----
WM_CONCAT(A.NAME)
--------------------------------------------------------------------
1
aaaa,bbbb
--注意顺序
with tab1 as(
select '1' id,'aaaa' name from dual union all
select '2','bbbb' from dual union all
select '3','cccc' from dual
),
tab2 as(
select 1 id ,'1,3,2' manager from dual union all
select 2 ,'2,3' from dual
)
select id,max(name) name from(
select b.id,wm_concat(a.name)over(partition by b.id order by instr(b.manager,a.id)) name
from tab1 a,tab2 b where instr(b.manager,a.id) > 0
)
group by id--结果 :
ID NAME
---------------
1 aaaa,cccc,bbbb
2 bbbb,cccc
你的这个sql和你楼下的实现的结果是一样的,但是当表a的id值有1,001,0001的时候,如果表b的manager存的是0001,那么查出的结果就是1和0001对应的name,怎么可以解决这个问题呢?在线等。谢谢……
2 select '1' id,'aaaa' name from dual union all
3 select '2','bbbb' from dual union all
4 select '3','cccc' from dual
5 ),
6 tab2 as(
7 select 1 id ,'1,3,2' manager from dual union all
8 select 2 ,'2,3' from dual
9 )
10 select id,max(name) name from(
11 select b.id,wm_concat(a.name)over(partition by b.id order by instr(','||b.manager||',',','||a.id||',')) name
12 from tab1 a,tab2 b where instr(b.manager,a.id) > 0
13 )
14 group by id;
ID NAME
---------- --------------------------------------------------------------------------------
1 aaaa,cccc,bbbb
2 bbbb,cccc
SQL> 加分隔符
with tab1 as(
select '1' id,'aaaa' name from dual union all
select '2','bbbb' from dual union all
select '3','cccc' from dual union all
select '11','ddd' from dual
),
tab2 as(
select 1 id ,'1,3,2' manager from dual union all
select 2 ,'2,3' from dual union all
select 3 ,'2,11' from dual
)
select id,max(name) name from(
select b.id,wm_concat(a.name) over(partition by b.id order by instr(b.manager,a.id)) name
from tab1 a,tab2 b where instr(','||b.manager||',',','||a.id||',') > 0
)
group by id