SELECT NT_ID,FULL_NM,EXP_DATE,SITE_USER,CRT_BY FROM TABLE1 ,(SELECT CNT_ID,wm_concat(CRT_BY) CRT_BY) FROM TABLE2 GROUP BY CNT_ID) TABLE2 WHERE TABLE1.CNT_ID=TABLE2.CNT_ID;
--也可以通过函数去实现 --可以参考blog:http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspxcreate or replace function my_concat(P_CNT_ID in number) return varchar2 is result varchar2(4000); begin for rs in (select CRT_BY from table2 where CNT_ID=P_CNT_ID) loop result :=result || rs.role || ','; end loop; result := rtrim(result,','); --去掉最后一个逗号 return result; end;SELECT NT_ID,FULL_NM,EXP_DATE,SITE_USER,my_concat(NT_ID) CRT_BY FROM TABLE1;
修改下上面的函数参数类型,应该是varchar2型(P_CNT_ID in varchar2)
with tb as (select cnt_id,wm_concat(CRT_BY) CRT_BY from tb2 group by cnt_id) select a.cnt_id,a.FULL_NM,a.EXP_DATE,a.SITE_USER,b.CRT_BY from TABLE1 a,tb b where a.cnt_id=b.cnt_id
参考: 有两个表,一主一子。 主表order: orderid ordertype 1 A 2 B 子表(order_car): orderid carno 1 10 1 20 2 30 我想写一条select 语句,得到如下效果 : orderid ordertype carno 1 A 10,20 2 B 30 不写存储过程,能否实现?大家写的应该都可以,不过我是在oracle 下面,居然专门有这样的函数wm_concat。 已经解决了。如下: select a.entrunstno, wm_concat(b.vechicleid) carnm from tbt_ent_information a join tbt_asn_zhuanche b on a.entrunstno =b.entrunstno where a.entrunstno ='0030000' group by a.entrunstno
SELECT NT_ID,FULL_NM,EXP_DATE,SITE_USER,CRT_BY
FROM TABLE1 ,(SELECT CNT_ID,wm_concat(CRT_BY) CRT_BY) FROM TABLE2 GROUP BY CNT_ID) TABLE2
WHERE TABLE1.CNT_ID=TABLE2.CNT_ID;
--也可以通过函数去实现
--可以参考blog:http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspxcreate or replace function my_concat(P_CNT_ID in number)
return varchar2
is
result varchar2(4000);
begin
for rs in (select CRT_BY from table2 where CNT_ID=P_CNT_ID) loop
result :=result || rs.role || ',';
end loop;
result := rtrim(result,','); --去掉最后一个逗号
return result;
end;SELECT NT_ID,FULL_NM,EXP_DATE,SITE_USER,my_concat(NT_ID) CRT_BY
FROM TABLE1;
with tb as
(select cnt_id,wm_concat(CRT_BY) CRT_BY from tb2 group by cnt_id)
select a.cnt_id,a.FULL_NM,a.EXP_DATE,a.SITE_USER,b.CRT_BY
from TABLE1 a,tb b
where a.cnt_id=b.cnt_id
有两个表,一主一子。
主表order: orderid ordertype
1 A
2 B 子表(order_car):
orderid carno
1 10
1 20
2 30 我想写一条select 语句,得到如下效果 : orderid ordertype carno
1 A 10,20
2 B 30 不写存储过程,能否实现?大家写的应该都可以,不过我是在oracle 下面,居然专门有这样的函数wm_concat。
已经解决了。如下:
select
a.entrunstno,
wm_concat(b.vechicleid) carnm
from
tbt_ent_information a
join
tbt_asn_zhuanche b
on
a.entrunstno =b.entrunstno
where a.entrunstno ='0030000'
group by
a.entrunstno