3,494
社区成员




select dense_rank() over(partition by parent_area order by area_name) area_id,
c.*
from (select distinct decode(b.num, 0, city, 1, area, 2, town) area_name,
b.num area_level_id,
decode(b.num, 0, '0', 1, city, 2, area) parent_area
from tareaname a,
(select 0 num
from dual
union all
select 1 num
from dual
union all
select 2 num from dual) b) c
order by area_level_id, area_id
SELECT DISTINCT LPAD (DENSE_RANK () OVER (ORDER BY city), 3, '0') area_id,
city area_name, 0 area_level_di, 0 parent_area_id
FROM tareaname
UNION ALL
SELECT DISTINCT LPAD (DENSE_RANK () OVER (ORDER BY city), 3, '0')
|| LPAD (DENSE_RANK () OVER (PARTITION BY city ORDER BY city,
area),
3,
'0'
) area_id,
area area_name, 1 area_level_di,
LPAD (DENSE_RANK () OVER (ORDER BY city),
3,
'0'
) parent_area_id
FROM tareaname
UNION ALL
SELECT DISTINCT LPAD (DENSE_RANK () OVER (ORDER BY city), 3, '0')
|| LPAD (DENSE_RANK () OVER (PARTITION BY city ORDER BY city,
area),
3,
'0'
)
|| LPAD (DENSE_RANK () OVER (PARTITION BY city, area ORDER BY city,
area, town),
3,
'0'
) area_id,
town area_name, 2 area_level_di,
LPAD (DENSE_RANK () OVER (ORDER BY city),
3,
'0'
)
|| LPAD (DENSE_RANK () OVER (PARTITION BY city ORDER BY city,
area),
3,
'0'
) parent_area_id
FROM tareaname
select *
from area
start with parent_area_id='0'
connect by piror area_id=parent_area_id