with t as ( select 'T023.0001' type_code,'技术类' type_name from dual union all select 'T023.0001.0001' type_code,'规划' type_name from dual union all select 'T023.0001.0001.0001' type_code,'国民经济和社会发展规划' type_name from dual union all select 'T024.0001' type_code,'网络类' type_name from dual ) select wm_concat(type_name) from t where t.type_code like 'T023%'应该这个表还有个字段能表示父子关系的吧?parent_type_code之类的?如果有这个字段的话,使用connect by会更好些
with t as ( select 'T023.0001' type_code,'技术类' type_name,'' parent_type_code from dual union all select 'T023.0001.0001' type_code,'规划' type_name,'T023.0001' from dual union all select 'T023.0001.0001.0001' type_code,'国民经济和社会发展规划' type_name,'T023.0001.0001' from dual union all select 'T024.0001' type_code,'网络类' type_name,'' from dual ) select RPAD( ' ', 2*(LEVEL-1), '-' ) || type_name "type_name", CONNECT_BY_ROOT type_name "ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL , SYS_CONNECT_BY_PATH(type_name, '/') "PATH" from t where t.type_code like 'T023%' start with t.parent_type_code is null connect by prior t.type_code=t.parent_type_code
我昨天想到一个方法,拿来给大家瞧瞧怎么优化,首先写一个函数: create or replace FUNCTION fun_type(var_type_code varchar2) RETURN varchar2 IS var_result varchar2(256); var_num number; BEGIN var_result := ''; var_num := 0; SELECT count(*) into var_num FROM t_category_info a WHERE a.type_code LIKE concat(var_type_code, '%'); if var_num = 1 then for i in (SELECT a.type_code, a.type_name FROM t_category_info a, t_category_info b WHERE b.type_code LIKE CONCAT(a.type_code, '%') AND b.type_code = var_type_code order by type_code) loop var_result := concat(var_result, concat(i.type_name, '.')); end loop; var_result := RTRIM(var_result, '."'); end if; RETURN var_result; END fun_type; 然后再调用这个函数: SELECT fun_type(a.type_code) FROM t_category_info a, t_category_info b WHERE a.type_code LIKE CONCAT(b.type_code, '%') AND b.type_code = 'T023';
(
select 'T023.0001' type_code,'技术类' type_name from dual
union all
select 'T023.0001.0001' type_code,'规划' type_name from dual
union all
select 'T023.0001.0001.0001' type_code,'国民经济和社会发展规划' type_name from dual
union all
select 'T024.0001' type_code,'网络类' type_name from dual
)
select wm_concat(type_name) from t
where t.type_code like 'T023%'应该这个表还有个字段能表示父子关系的吧?parent_type_code之类的?如果有这个字段的话,使用connect by会更好些
(
select 'T023.0001' type_code,'技术类' type_name,'' parent_type_code from dual
union all
select 'T023.0001.0001' type_code,'规划' type_name,'T023.0001' from dual
union all
select 'T023.0001.0001.0001' type_code,'国民经济和社会发展规划' type_name,'T023.0001.0001' from dual
union all
select 'T024.0001' type_code,'网络类' type_name,'' from dual
)
select RPAD( ' ', 2*(LEVEL-1), '-' ) || type_name "type_name",
CONNECT_BY_ROOT type_name "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(type_name, '/') "PATH"
from t
where t.type_code like 'T023%'
start with t.parent_type_code is null
connect by prior t.type_code=t.parent_type_code
create or replace FUNCTION fun_type(var_type_code varchar2) RETURN varchar2 IS
var_result varchar2(256);
var_num number;
BEGIN
var_result := '';
var_num := 0;
SELECT count(*)
into var_num
FROM t_category_info a
WHERE a.type_code LIKE concat(var_type_code, '%');
if var_num = 1 then
for i in (SELECT a.type_code, a.type_name
FROM t_category_info a, t_category_info b
WHERE b.type_code LIKE CONCAT(a.type_code, '%')
AND b.type_code = var_type_code
order by type_code) loop
var_result := concat(var_result, concat(i.type_name, '.'));
end loop;
var_result := RTRIM(var_result, '."');
end if;
RETURN var_result;
END fun_type;
然后再调用这个函数:
SELECT fun_type(a.type_code)
FROM t_category_info a, t_category_info b
WHERE a.type_code LIKE CONCAT(b.type_code, '%')
AND b.type_code = 'T023';