creare function get_str(p_xmmc in varchar2) return varchar2 as cursor t_sor is select mc from bm a where instr(p_xmmc,a.bm)>0; str varchar2(50); num number:=0; begin for v_sor in t_sor loop if num=0 then str:=v_sor.mc; num:=num+1; else str:=str||'-'||v_sor.mc; end if; end loop; return str; end; / select xmmc,get_str(bm) from xm;
select a.bm,a.mc from (select a.bm,a.mc||nvl(b.mc,'') mc from (select a.bm,a.mc||nvl(b.mc,'') from tablename a, (select b.* from tablename b where length(b.bm)=4) b where a.bm=b.bm(+)) a, (select b.* from tablename b where length(b.bm)=6) c where a.bm=c.bm(+)) a, xm where a.bm=xm.bm
create table bm(id int , bm varchar(32), mc nvarchar(32)) insert into bm values(1, '01', '支出') insert into bm values(2, '0101', '基本支出') insert into bm values(3, '010101', '一般支出') insert into bm values(4, '0102', '其他出') insert into bm values(5, '02', '收入') insert into bm values(6, '0201', '一般收入')create table xm(xmmc nvarchar(32), bm varchar(32)) insert into xm values('项目1', '010101') insert into xm values('项目2', '0201')drop function Getbmmc gocreate function Getbmmc(@bm varchar(32)) returns varchar(300) as begin declare @re varchar(300), @parent varchar(32) --得到编码累计 select @parent=@bm, @re=mc ,@parent=case when len(bm)>2 then substring(bm, 1, len(bm)-2) else null end from bm where bm=@bm while @parent is not null begin select @re=mc+'-'+@re ,@parent=case when len(bm)>2 then substring(bm, 1, len(bm)-2) else null end from bm where bm=@parent end return @reend goselect xmmc, dbo.Getbmmc(bm) from xm ----- output ------- 项目1 支出-基本支出-一般支出 项目2 收入-一般收入
使用sys_connect_by_path需要使用父子树型结构,请参考下面的sqlSQL> select * from bb1;ITEM_ID ITEM_NAME ---------- -------------------- 01 A 0101 BB 0102 sda 010201 dsfa 010202 asda 02 DS 0201 asda 0202 ASSda8 rows selected. 1 select i_id, item_name,SYS_CONNECT_BY_PATH(item_name, '-') i_name 2 from (select item_id i_id,substr(item_id,1,length(item_id)-2) p_id, item_name from bb1) 3* connect by prior i_id = p_id start with p_id is null SQL> /I_ID ITEM_NAME I_NAME ---------- -------------------- ------------------------------ 01 A -A 0101 BB -A-BB 0102 sda -A-sda 010201 dsfa -A-sda-dsfa 010202 asda -A-sda-asda 02 DS -DS 0201 asda -DS-asda 0202 ASSda -DS-ASSda8 rows selected.
刚才的测试基于sql server 2000的,oracle可能要改改
在上面sql外套一个select 与tab2连接:select xmmc,bmmc from tab2, (select bm, sys_connect_by,path(a.mc, '-') bmmc from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from tab1) connect by prior bm = p_bm start with p_bm is null) a where tab2.bm = a.bm;
更正 在上面sql外套一个select 与tab2连接:select xmmc,bmmc from tab2, (select bm, sys_connect_by_path(mc, '-') bmmc from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from tab1) connect by prior bm = p_bm start with p_bm is null) a where tab2.bm = a.bm;
测试结果: SQL> select * from bm; ID BM MC ---------- -------------------------------- -------------------------------- 1 01 wqe 2 0101 qewq 3 010101 qew 4 0102 weq 5 02 eeqr 6 0201 dsdfa6 rows selected.SQL> select * from xm;XMMC BM -------------------------------- -------------------------------- item1 010101 item2 0201SQL> SQL> select xmmc,bmmc 2 from xm, 3 (select bm, sys_connect_by_path(mc, '-') bmmc 4 from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from bm) 5 connect by prior bm = p_bm start with p_bm is null) a 6 where xm.bm = a.bm;XMMC BMMC -------------------------------- ------------------------------ item1 -wqe-qewq-qew item2 -eeqr-dsdfaSQL>
以下可得出你要的结果,并测试通过:create table bm (id integer,bm varchar2(30),mc varchar2(50))create table xm (xmmc varchar2(10),bm varchar2(30))begin insert into bm values('0', '0', 'the origin'); insert into bm values('1', '01', '支出'); insert into bm values('2', '0101', '基本支出'); insert into bm values('3', '010101', '一般支出'); insert into bm values('4', '0102', '其他出'); insert into bm values('5', '02', '收入'); insert into bm values('6', '0201', '一般收入'); insert into xm values('项目1', '010101'); insert into xm values('项目2', '0201'); end;select b.xmmc,a.path from ( select bm,ltrim(SYS_CONNECT_BY_PATH(mc, '-'),'-the origin') path from ( select a.bm,a.mc,decode(a.bm,'0',null,b.bm) sjbm from bm a,bm b where instr(a.bm,b.bm)=1 and length(a.bm)-length(b.bm)<=2 and (length(a.bm)!=length(b.bm) or a.bm='0') ) start with bm='0' connect by prior bm=sjbm ) a,xm b where a.bm=b.bm
不过事情还是要做的。
多谢!
一个sql语句绝对没有问题,可以处理,你给多少分?*_*
creare function get_str(p_xmmc in varchar2)
return varchar2
as
cursor t_sor is
select mc from bm a where instr(p_xmmc,a.bm)>0;
str varchar2(50);
num number:=0;
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.mc;
num:=num+1;
else
str:=str||'-'||v_sor.mc;
end if;
end loop;
return str;
end;
/
select xmmc,get_str(bm) from xm;
1 01 支出 支出
2 0101 基本支出 支出-基本支出
3 010101 一般支出 支出-基本支出-一般支出
4 0102 其他出 支出-其他出
5 02 收入 收入
6 0201 一般收入 收入-一般收入
...
http://expert.csdn.net/Expert/topic/2632/2632114.xml?temp=8.149356E-02
from
(select a.bm,a.mc||nvl(b.mc,'') mc
from
(select a.bm,a.mc||nvl(b.mc,'')
from tablename a,
(select b.*
from tablename b
where length(b.bm)=4) b
where a.bm=b.bm(+)) a,
(select b.*
from tablename b
where length(b.bm)=6) c
where a.bm=c.bm(+)) a,
xm
where a.bm=xm.bm
insert into bm values(1, '01', '支出')
insert into bm values(2, '0101', '基本支出')
insert into bm values(3, '010101', '一般支出')
insert into bm values(4, '0102', '其他出')
insert into bm values(5, '02', '收入')
insert into bm values(6, '0201', '一般收入')create table xm(xmmc nvarchar(32), bm varchar(32))
insert into xm values('项目1', '010101')
insert into xm values('项目2', '0201')drop function Getbmmc
gocreate function Getbmmc(@bm varchar(32))
returns varchar(300)
as
begin
declare @re varchar(300), @parent varchar(32) --得到编码累计 select @parent=@bm, @re=mc
,@parent=case when len(bm)>2 then substring(bm, 1, len(bm)-2)
else null end
from bm where bm=@bm while @parent is not null
begin
select @re=mc+'-'+@re
,@parent=case when len(bm)>2 then substring(bm, 1, len(bm)-2)
else null end
from bm where bm=@parent
end return @reend
goselect xmmc, dbo.Getbmmc(bm) from xm
----- output -------
项目1 支出-基本支出-一般支出
项目2 收入-一般收入
---------- --------------------
01 A
0101 BB
0102 sda
010201 dsfa
010202 asda
02 DS
0201 asda
0202 ASSda8 rows selected. 1 select i_id, item_name,SYS_CONNECT_BY_PATH(item_name, '-') i_name
2 from (select item_id i_id,substr(item_id,1,length(item_id)-2) p_id,
item_name from bb1)
3* connect by prior i_id = p_id start with p_id is null
SQL> /I_ID ITEM_NAME I_NAME
---------- -------------------- ------------------------------
01 A -A
0101 BB -A-BB
0102 sda -A-sda
010201 dsfa -A-sda-dsfa
010202 asda -A-sda-asda
02 DS -DS
0201 asda -DS-asda
0202 ASSda -DS-ASSda8 rows selected.
from tab2,
(select bm, sys_connect_by,path(a.mc, '-') bmmc
from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from tab1)
connect by prior bm = p_bm start with p_bm is null) a
where tab2.bm = a.bm;
在上面sql外套一个select 与tab2连接:select xmmc,bmmc
from tab2,
(select bm, sys_connect_by_path(mc, '-') bmmc
from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from tab1)
connect by prior bm = p_bm start with p_bm is null) a
where tab2.bm = a.bm;
SQL> select * from bm; ID BM MC
---------- -------------------------------- --------------------------------
1 01 wqe
2 0101 qewq
3 010101 qew
4 0102 weq
5 02 eeqr
6 0201 dsdfa6 rows selected.SQL> select * from xm;XMMC BM
-------------------------------- --------------------------------
item1 010101
item2 0201SQL>
SQL> select xmmc,bmmc
2 from xm,
3 (select bm, sys_connect_by_path(mc, '-') bmmc
4 from (select bm,substr(bm,1,length(bm)-2) p_bm,mc from bm)
5 connect by prior bm = p_bm start with p_bm is null) a
6 where xm.bm = a.bm;XMMC BMMC
-------------------------------- ------------------------------
item1 -wqe-qewq-qew
item2 -eeqr-dsdfaSQL>
insert into bm values('0', '0', 'the origin');
insert into bm values('1', '01', '支出');
insert into bm values('2', '0101', '基本支出');
insert into bm values('3', '010101', '一般支出');
insert into bm values('4', '0102', '其他出');
insert into bm values('5', '02', '收入');
insert into bm values('6', '0201', '一般收入');
insert into xm values('项目1', '010101');
insert into xm values('项目2', '0201');
end;select b.xmmc,a.path from
(
select bm,ltrim(SYS_CONNECT_BY_PATH(mc, '-'),'-the origin') path
from
(
select a.bm,a.mc,decode(a.bm,'0',null,b.bm) sjbm
from bm a,bm b
where instr(a.bm,b.bm)=1 and length(a.bm)-length(b.bm)<=2
and (length(a.bm)!=length(b.bm) or a.bm='0')
)
start with bm='0' connect by prior bm=sjbm
) a,xm b where a.bm=b.bm