with a as(
select '1001' id,'张三' name, 'uu' from dual union all
select '1001' id,'张三' name, 'uu' from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
b as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.id,
a.name,
a.,
beingTracked,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select id || ' ' || name || ' ' ||
from a
where a.id = b.tracked) tracked
from b) c,
a
where c.beingTracked = a.id
group by a.id, a.name, a., beingTracked
select '1001' id,'张三' name, 'uu' from dual union all
select '1001' id,'张三' name, 'uu' from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
b as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.id,
a.name,
a.,
beingTracked,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select id || ' ' || name || ' ' ||
from a
where a.id = b.tracked) tracked
from b) c,
a
where c.beingTracked = a.id
group by a.id, a.name, a., beingTracked
解决方案 »
- 我的oracle的存储过程总报错,请高手帮帮忙!
- oracle安装的时候总是提示身份证明检索失败
- SUSE11.1安装oracle 10g报错。Exception in thread
- 请教一个分类统计排名SQL语句
- 表间有关系,我如何才能执行如下的语句。
- 一个SQL查询问题,大家关注一下!
- 请问ORA-12560:TNS:协议适配器错误是怎么回事?紧急啊!!!!
- 为什么我用sqlldr导入数据后,数据显示为乱码?
- VNI-2015 : 目标节点的节点首选身份证明无效
- 我该如何恢复这个数据库呢
- Asp.NET调用存储过程报PLS-00201: identifier must be declared错误的问题
- OracleServiceORCL服务过一段时间需要重启,否则连接超时?
t as (select 1001 ID,'张三' Name,'uu' Mark from dual
union all
select 1002 ID,'李四' Name,'pp' Mark from dual
union all
select 1003 ID,'王五' Name,'kk' Mark from dual),
t1 as (select 1001 beingTracked,1002 tracked from dual
union all
select 1001 beingTracked,1003 tracked from dual),
t2 as (select t.ID,t.Name,t.Mark,t1.tracked from t,t1 where t.ID=t1.beingTracked),
t3 as (select t2.ID,t2.Name,t2.Mark,'('||t.ID||' '||t.Name||' '||t.Mark||')' as hebing from t,t2 where t.ID=t2.tracked)
select t3.ID,t3.Name,t3.Mark,to_char(wm_concat(t3.hebing)) as "合并"
from t3
group by t3.ID,t3.Name,t3.Mark;
select '1001' id,'张三' name, 'uu' from dual union all
select '1001' id,'张三' name, 'uu' from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
b as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.id,
a.name,
a.,
beingTracked,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select id || ' ' || name || ' ' ||
from a
where a.id = b.tracked) tracked
from b) c,
a
where c.beingTracked = a.id
group by a.id, a.name, a., beingTracked我想问下,我的表A里面的数据是动态的,字段是差不多的。 我上面写的只是个例子。 您的方法能用么?
select '1001' id,'张三' name, 'uu' from dual union all
select '1001' id,'张三' name, 'uu' from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
b as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.id,
a.name,
a.,
beingTracked,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select id || ' ' || name || ' ' ||
from a
where a.id = b.tracked) tracked
from b) c,
a
where c.beingTracked = a.id
group by a.id, a.name, a., beingTracked用你的方法替换到我的表里, 怎么‘合并’那一列没有数据。
select '1001' id,'张三' name, 'uu' from dual union all
select '1001' id,'张三' name, 'uu' from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
b as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.id,
a.name,
a.,
beingTracked,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select id || ' ' || name || ' ' ||
from a
where a.id = b.tracked) tracked
from b) c,
a
where c.beingTracked = a.id
group by a.id, a.name, a., beingTracked用你的方法替换到我的表里, 怎么‘合并’那一列没有数据。
不能啊,你怎么写的。。
select a.id,a.name,a.,wmsys.wm_concat(distinct c.tracked) 合并 from
(select beingTracked,(select a.id || ' ' || a.name || '(' || a. || ')' from a
where a.id = b.tracked)tracked from b) c,a where c.beingTracked = a.id
group by a.id, a.name, a. 我就用了这一段, 表a和表b在我的数据库是已经存在的。
我把所有的a替换成我的表section,b也替换成我的表sectionTrack了。 但是合并字段没数据。select a.sectionid,a.sectionname,a.section, wmsys.wm_concat(distinct c.tracked) 合并 from
(select beingTracked,(select a.sectionid || ' ' || a.sectionname || '(' || a.section || ')' from section a
where a.sectionid = b.tracked)tracked from Sectiontrack b) c, section a where c.beingTracked = a.sectionid
group by a.sectionid, a.sectionname, a.section
确定section.sectionid=Sectiontrack.tracked对应关系没错么。。
select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' || a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b
select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' || a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b能查询到值。
select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' || a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b能查询到值。
select a.sectionid,
a.sectionname,
a.section
from (select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' ||
a.section || ')'
from section a
where asection.sectionid = bSectiontrack.tracked) tracked
from Sectiontrack b) c,
section a
where c.beingTracked = a.sectionid
看看这个能不能查到值
a.sectionname,
a.section
from (select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' ||
a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b) c,
section a
where c.beingTracked = a.sectionid
对不起,我没有表结构没法试验,只能你试验一下了。。
试试上面的sql
a.sectionname,
a.section
from (select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' ||
a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b) c,
section a
where c.beingTracked = a.sectionid
对不起,我没有表结构没法试验,只能你试验一下了。。
试试上面的sql这个可以查询到。
我的表结构:
表a如下:
表名:section
字段:sectionid sectionname section表b如下:
表名:sectiontrack
字段:beingTracked tracked
select t0.begintracked,wmsys.wm_concat(t0.ed)
from (
select t1.begintracked, '('||t0.id ||t0.name ||t0.||')' as ed from Table1 t0
inner join
(
select t1.* from Table1 t0
inner join Table2 t1 on t0.id=t1.begintracked
) t1 on t0.id=t1.tracked
) t0
group by t0.begintracked
改成我的之后, 合并的那列就是没数据。纠结。
你oracle什么版本。。
改成我的之后, 合并的那列就是没数据。纠结。
没发现什么问题,要不你就把真实数据贴出来几条,我再帮你改写一个sql试试。。
改成我的之后, 合并的那列就是没数据。纠结。
你oracle什么版本。。11gR2
改成我的之后, 合并的那列就是没数据。纠结。
没发现什么问题,要不你就把真实数据贴出来几条,我再帮你改写一个sql试试。。
真实数据也是胡乱添加的。
section表存数据 sectiontrack表存section表的数据之间的追踪关系, 所以只存section表的sectionid
改成我的之后, 合并的那列就是没数据。纠结。
没发现什么问题,要不你就把真实数据贴出来几条,我再帮你改写一个sql试试。。
真实数据也是胡乱添加的。
section表存数据 sectiontrack表存section表的数据之间的追踪关系, 所以只存section表的sectionid
with section as(select '1001' sectionid,'张三' sectionname, 'uu' section from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
sectiontrack as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.sectionid,
a.sectionname,
a.section,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' ||
a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b) c,
section a
where c.beingTracked = a.sectionid
group by a.sectionid, a.sectionname, a.section
我用你的sql和你之前举得例子做的测试没有任何问题。。
你看看是不是你表数据弄反了,比如b表中的追踪关系是不是反了,或者两个表弄反了。。
改成我的之后, 合并的那列就是没数据。纠结。
没发现什么问题,要不你就把真实数据贴出来几条,我再帮你改写一个sql试试。。
真实数据也是胡乱添加的。
section表存数据 sectiontrack表存section表的数据之间的追踪关系, 所以只存section表的sectionid
with section as(select '1001' sectionid,'张三' sectionname, 'uu' section from dual union all
select '1002','李四','pp' from dual union all
select '1003','王五','kk' from dual),
sectiontrack as(
select '1001' beingTracked,'1002' tracked from dual union all
select '1001','1003' from dual)
select a.sectionid,
a.sectionname,
a.section,
wmsys.wm_concat(distinct c.tracked) 合并
from (select beingTracked,
(select a.sectionid || ' ' || a.sectionname || '(' ||
a.section || ')'
from section a
where a.sectionid = b.tracked) tracked
from Sectiontrack b) c,
section a
where c.beingTracked = a.sectionid
group by a.sectionid, a.sectionname, a.section
我用你的sql和你之前举得例子做的测试没有任何问题。。
你看看是不是你表数据弄反了,比如b表中的追踪关系是不是反了,或者两个表弄反了。。会不会是我的Oracle版本问题。 我的是11gR2
(
id NUMBER not null,
name VARCHAR2(10),
VARCHAR2(10)
);
insert into TESTA (id, name, )
values (1001, 'jam', 'ui');
insert into TESTA (id, name, )
values (1002, 'jack', 'kk');
insert into TESTA (id, name, )
values (1003, 'tim', 'ww');
insert into TESTA (id, name, )
values (1004, 'hls', 'jj');
insert into TESTA (id, name, )
values (1444, '1111', '1111');create table TESTB
(
betrack NUMBER,
track NUMBER
);insert into TESTB (betrack, track)
values (1001, 1002);
insert into TESTB (betrack, track)
values (1001, 1003);
insert into TESTB (betrack, track)
values (1004, 1002);
insert into TESTB (betrack, track)
values (1002, 1003);
commit;with
first_re (id, track ,merge_c) as (
select a.id,a.track, ('('||b.id||','||b.name||','||b.||')') as merge_c
from
(select ta.id as id ,tb.track as track
from TESTA ta left join TESTB tb on ta.id=tb.betrack
WHERE EXISTS (SELECT * FROM TESTB tb WHERE ta.id=tb.betrack )) a
left join TESTA b on a.track = b.id
),
t_mer (id ,mergec) as(
select id,merge_result from
(select id, merge_result, row_number() over(partition by id order by id,cur_lvl desc) as lv FROM (SELECT f.id as id,
f.rank as rank,
level as cur_lvl,
ltrim(sys_connect_by_path(f.merge_c,'-'),'-') merge_result
FROM
(select t.id as id,
t.merge_c as merge_c,
row_number() over(partition by t.id order by t.id) rank
from first_re t order by t.id) f
connect by f.id = prior f.id and f.rank-1=prior f.rank))
where lv = 1
)
select * from t_mer;说明一下ltrim(sys_connect_by_path(f.merge_c,'-'),'-')这里用‘-’的原因,你自己去的换成‘,'试试,会报错的,原因是sys_connect_by_path的原因,你在出力结果中使用replace方法再处理一下就ok啦。
其他的自己字段,自己看怎么加上去哈。
用你的方法我试验出来了,可行, 但是43行t_mer(id,mergec)如何加几个字段变成t_mer(id,name,,mergec)?
我还得查name和
select a.id,
('('||b.id||','||b.name||','||b.||')') as merge_c,
row_number() over(partition by a.id order by rownum) rn
from
(select ta.id as id,
tb.track as track
from TESTA ta
left join TESTB tb
on ta.id=tb.betrack
WHERE EXISTS (SELECT * FROM TESTB tb WHERE ta.id=tb.betrack)) a
left join TESTA b on a.track = b.id
),
merge_result(id,track_merge,rn) as (
select id,'' as track_merge ,0 as rn from (select distinct id from result_emp)
union all
select r.id, ltrim(m.track_merge||','||r.merge_c,','), r.rn
from result_emp r, merge_result m
where r.id = m.id
and r.rn-1 = m.rn
)
select * from merge_result a right join
(select id ,max(rn)as rn from merge_result group by id) b on a.id = b.id and a.rn = b.rn;
用你的方法我试验出来了,可行, 但是43行t_mer(id,mergec)如何加几个字段变成t_mer(id,name,,mergec)?
我还得查name和
哥哥,加几个字段,不就是在select 中加嘛,去学习下with as的用法,对自己有好处,你看我id都加进去了,其他的字段不用做什么处理,怎么加自己想想哈。我也是临时看的with as 的用法的。