表A,
字段A,B
字段A内容为','搁开的字符串(每个搁开的值对应表AA的A1字段)
A B
13450,1234,145,643, X表AA
字段A1,A2 主键为A1
A1 A2
13450 T
1234 TT
145 TTT
643 TTTT能不能有个sql,执行好更新表A得到:A B
T,TT,TTT,TTTT X
字段A,B
字段A内容为','搁开的字符串(每个搁开的值对应表AA的A1字段)
A B
13450,1234,145,643, X表AA
字段A1,A2 主键为A1
A1 A2
13450 T
1234 TT
145 TTT
643 TTTT能不能有个sql,执行好更新表A得到:A B
T,TT,TTT,TTTT X
解决方案 »
- ORACLE,求助~~
- 请教一个sql语句问题?
- 对于同个网卡不同端口的侦听器会采用均衡负载吗?
- 高手帮忙:数据库发布管理问题?
- 新手建表空间的问题
- 在windows2003下装Oracle8.16有问题,请各位高手紧急救援!
- 关于临时表的几个小问题
- orace_developer
- 文一个好笑的问题 ?我想请问一下Oracle怎么登陆呀!:)在线等待!
- 急!!!高分求解!!!IMP-00016: 不支持要求的字符集转换(从类型850到852)问题!
- .NET ORA-12154: TNS: 无法处理服务名(怪,疯!!!)
- 遇到一个很棘手的循环查询的问题,大家一起来集思广益啊~~我觉得这个比较挑战能力(关注有分啊)
v_temp varchar(30);
cursor a1_cur is
select A2 from AA;
begin
for a1_info in a1_cur
loop
v_temp := v_temp||','||a1_info.A2;
end loop;
update A
set A=v_temp
where B='X';
end;
我这里没有Oracle
A B
13450,1234,145,643, X
然后把游标的值取出来,通过v_temp := v_temp||','||a1_info.A2;
V_temp的值就应该是T,TT,TTT,TTTT
declare
ls_tem varchar(50);
ls_new varchar(15);
ls_pat varchar(15);
li_pos number(3);
li_pre number(3);
begin
for cr_cur in (select A || ',' as A, B from A) loop
ls_tem := '';
li_pre := 1;
li_pos :=instr(cr_cur.A, ',', li_pre);
while li_pos >0 loop
ls_pat := substr(cr_cur.A, li_pre, li_pos - li_pre);
select A2 into ls_new from AA where A1 = ls_pat;
ls_tem := ls_tem || ',' || ls_new;
li_pre := li_pos + 1;
li_pos := instr(cr_cur.A, ',', li_pre);
end loop;
update A set A = ls_tem where A = cr_cur.A;
end loop;
end;
update A set A = ls_tem where A = cr_cur.A;
V_temp的值就应该是T,TT,TTT,TTTT,这个是没有错,但是如果,表A,字段A内容为','搁开的字符串,这些搁开的子串not in 表AA的A1字段,不应该更新到表A的字段A中,
比如:
表A,
字段A,B
字段A内容为','搁开的字符串(每个搁开的值对应表AA的A1字段)
A B
13450,1234,145 X表AA
字段A1,A2 主键为A1
A1 A2
13450 T
1234 TT
145 TTT
643 TTTT
输出应该为T,TT,TTT,不应该为T,TT,TTT,TTTT,因为TTTT对应的643 not in 表A 的字段A(搁开后)
v_temp varchar(30);
cursor a1_cur is
select A2 from AA;
begin
for a1_info in a1_cur
loop
v_temp := v_temp||','||a1_info.A2;
end loop;
update A
set A=v_temp
where B='X';
end;
这样是可以的,我测试了,只不过多了一个“,”,其他的都是正确的!
as
ls_tem varchar(50);
ls_new varchar(15);
ls_pat varchar(15);
li_pos number(3);
li_pre number(3);
begin
for cr_cur in (select A || ',' as A, B from A) loop
ls_tem := '';
li_pre := 1;
li_pos :=instr(cr_cur.A, ',', li_pre);
while li_pos >0 loop
ls_pat := substr(cr_cur.A, li_pre, li_pos - li_pre);
select A2 into ls_new from AA where A1 = ls_pat;
ls_tem := ls_tem || ',' || ls_new;
li_pre := li_pos + 1;
li_pos := instr(cr_cur.A, ',', li_pre);
end loop;
ls_tem := trim(',' from ls_tem);
update A set A = ls_tem where A = trim(',' from cr_cur.A);
end loop;
end;
a = (
select f_conc( aa.a2 ) from aa, table(f_sep( a.a )) c
where c.c=aa.a1
)
我的要求是這樣有一個表
tableA
col1, col2
30a 30
32aa 50
40d 25要求用一個SQl 生成
30a/32aa/40d 105
(
select scol2,max(decode(col1,'30a',col1,null)) co,
max(decode(col1,'32aa',col1,null)) ct,
max(decode(col1,'40d',col1,null)) ch
from (select col1,sum(col2) over() as scol2 from cta) group by scol2
)
里面的cta是tableA
問題是 記錄不是固定的
(
select col1||','||col2||','||col3 tcol
from
(
select chgb,max(decode(chga,'T',chga)) col1, max(decode(chga,'TT',chga)) col2, max(decode(chga,'TTT',chga)) col3
from
(
select chgb,chga
from
(
select totala.b chgb, csdn_aa.a2 chga
from
(
select b,substr(a,1,instr(a,',',1,1)-1) as name from csdn_a
union all
select b,substr(a,length(substr(a,1,instr(a,',',1,1)))+1,(length(substr(a,1,instr(a,',',1,2)))-1) - (length(substr(a,1,instr(a,',',1,1))))) as name
from csdn_a
union all
select b,substr(a,length(substr(a,1,instr(a,',',1,2)))+1,(length(substr(a,1,instr(a,',',1,3)))-1) - (length(substr(a,1,instr(a,',',1,2))))) as name
from csdn_a
union all
select b,substr(a,length(substr(a,1,instr(a,',',1,3)))+1,(length(substr(a,1,instr(a,',',1,4)))-1) - (length(substr(a,1,instr(a,',',1,3))))) as name
from csdn_a
)totala ,csdn_aa
where totala.name = csdn_aa.A1
order by chga
)
)
group by chgb
)
)
where b = 'X'