有一张编码表bm
id bm mc
1 01 支出
2 0101 基本支出
3 010101 一般支出
4 0102 其他出
5 02 收入
6 0201 一般收入
... ...
我在另一张表(xm)引用了编码表
xmmc bm
项目1 010101
项目2 0201
。。。
现在要求做个查询，要求bmmc能逐级显示，即能显示上级的名称，
能显示如下：
xmmc bmmc
项目1 支出-基本支出-一般支出
项目2 收入-一般收入
这个SQL怎么写？
多谢！
---------------------------------------------------------------
我认为不用费力气了，一个sql语句绝对不能完成的。
写一段pl/sql就Ok了。
---------------------------------------------------------------
use procedure
---------------------------------------------------------------
up
---------------------------------------------------------------
procedure
---------------------------------------------------------------
function or procedure
---------------------------------------------------------------
是呀！还是用过程来写，用游标把数据库取出再处理。。。
---------------------------------------------------------------
用plus/sql 用一个什么 ...path(),函数想不起来了
---------------------------------------------------------------
一條SQL語句我想比較的難，最好是用遊標把數據取出來在查詢就回好的多了！
---------------------------------------------------------------
to: hrb_qiuyb(晨钟暮鼓)
一个sql语句绝对没有问题，可以处理，你给多少分？_
---------------------------------------------------------------
是的..用path()可以实现
---------------------------------------------------------------
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;
---------------------------------------------------------------
用PL/SQL吧
---------------------------------------------------------------
按照我的理解，代码表应该是比较固定的吧？那么从效率考虑，还不如在该表上加入另一个字段来记录整个名称。
id bm mc gmc
1 01 支出 支出
2 0101 基本支出 支出-基本支出
3 010101 一般支出 支出-基本支出-一般支出
4 0102 其他出 支出-其他出
5 02 收入 收入
6 0201 一般收入 收入-一般收入
...
---------------------------------------------------------------
要是编码类型不多的话可以考虑楼上的思路，不过，适用性就差了
---------------------------------------------------------------
用递归查询，调用sys_connect_by_path，一句sql 就可以了
---------------------------------------------------------------
请问楼上怎么用这个函数的？
---------------------------------------------------------------
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
go
create 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 @re
end
go
select xmmc, dbo.Getbmmc(bm) from xm
----- output -------
项目1 支出-基本支出-一般支出
项目2 收入-一般收入
---------------------------------------------------------------
使用sys_connect_by_path需要使用父子树型结构，请参考下面的sql
SQL> select * from bb1;
ITEM_ID ITEM_NAME
---------- --------------------
01 A
0101 BB
0102 sda
010201 dsfa
010202 asda
02 DS
0201 asda
0202 ASSda
8 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-ASSda
8 rows selected.
---------------------------------------------------------------
在上面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 dsdfa
6 rows selected.
SQL> select * from xm;
XMMC BM
-------------------------------- --------------------------------
item1 010101
item2 0201
SQL>
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-dsdfa
SQL>
---------------------------------------------------------------
以下可得出你要的结果，并测试通过:
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 val