自己修改一下,我的数据不支持中文:with a as
(select '00001' bh, 'qq' xm, '2014/05/05' csrq, '1-m' xb
from dual
union all
select '00002' bh, 'qq1' xm, '2014/05/05' csrq, '1-f' xb
from dual),
b as
(select '00001' bh, 1 xh, 'zhangsan1' ld, '2014/05/05' blrq
from dual
union all
select '00002' bh, 2 xh, 'zhangsan2' ld, '2014/05/05' blrq
from dual
union all
select '00001' bh, 3 xh, 'zhangsan3' ld, '2014/05/06' blrq
from dual
union all
select '00002' bh, 4 xh, 'zhangsan1' ld, '2014/05/07' blrq
from dual),
c as
(select 1 xh, 'shichangdiaoyan' c2
from dual
union all
select 1 xh, 'diaoyan' c2
from dual
union all
select 1 xh, 'qita' c2
from dual
union all
select 2 xh, 'shichangdiaoyan' c2
from dual
union all
select 3 xh, 'diaoyan' c2
from dual
union all
select 4 xh, 'qita' c2
from dual
union all
select 4 xh, 'yanjiu' c2
from dual)
select *
from a,
b,
(select xh, replace(to_char(wm_concat(c2)), ',', ' ') c3
from (select xh, rn || '.' || c2 c2
from (select xh,
c2,
row_number() over(partition by xh order by 1) rn
from c))
group by xh) c
where a.bh = b.bh
and b.xh = c.xh
and c.c3 like '%diaoyan%'
order by b.xh;
解决方案 »
- oracle 日志文件夹下的trm后缀文件是干嘛的?
- 主键和唯一索引的字段可否相同?
- Oracle - OraClient10g_home1里的enterprise manager console 重启电脑后 不见了
- oracle 无法处理服务名
- ORACLE Transparent GateWay相关信息大讨论,跟贴有分!!!
- 如何從ORACLE中導出表中的數據到ACCESS?(在線等待)
- Oracle9iAS安装问题!
- 怎么备份库和恢复库?
- 如何取分组后的每组的最大值的问题。
- 在oracle中如何实现单表的自我更新,比如本月的数据根据上月的数据进行update? 如何写update语句?
- DMP数据导入错误
- 如何SQL语句去除两列重复?
select *
from (select b.bh,
b.xh,
a.xm,
a.csrq,
a.xb,
b.ld,
b.blrq,
to_char(wm_concat(c.c2)) as c2
from a,b,c
where a.bh = b.bh
and b.xh = c.xh
group by b.bh, b.xh, a.xm, a.csrq, a.xb, b.ld, b.blrq
order by b.xh)
where c2 like '%调研%'
/* AA是表a,AB是表b,AC是表c */
select a2.BH,a2.XH,a1.XM,round(months_between(sysdate,a1.CSRQ)/12) N_L,
a1.XB,a2.LD,a2.blrq,a3.C_2 from AA a1,AB a2,
(select a2.XH,wm_concat(a3.C2) C_2 from AB a2,AC a3 where a2.XH=a3.XH group by a2.XH) a3
where a1.BH=a2.BH and a2.XH=a3.XH and C_2 like '%调研%';