SELECT supplier_id,
(SELECT area_id
FROM crm_area_country
WHERE area_type = 'office' START
WITH area_id = c.supplier_country_id CONNECT BY prior area_parent_id = area_id
------解决方案--------------------
视图：CREATE OR REPLACE VIEW VW_COUNTRY_POPEDOM_HW AS
SELECT user_id, psa.vendor_id supplier_id,popedom_type --DISTINCT
FROM po_vendor_sites_all psa,
(SELECT area_id,
area_code,
area_correspond_code,
area_chinese_name,
area_type,
user_id
,popedom_type
FROM (SELECT c.area_id,--地区部的权限范围
area_code,
area_correspond_code,
area_chinese_name,
area_type,
c.popedom_user user_id
,c.popedom_type
FROM crm_popedom c,
(SELECT area_id,
area_code,
area_correspond_code,
area_chinese_name,
area_type,
area_parent_id,
(rtrim(substr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
0,
instr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
',',
1)),
','))
AS root_area_id
FROM crm_area_country ca
START WITH ca.area_parent_id IN (SELECT area_id FROM crm_popedom)
CONNECT BY PRIOR ca.area_id = ca.area_parent_id) a
WHERE a.root_area_id = c.area_id or a.area_parent_id=c.area_id )
WHERE area_type = 'Country') ca --,po_vendors_all pa
WHERE psa.country = ca.area_correspond_code
union all
select cac.popedom_user user_id, supplier_id, popedom_type
from (select supplier_id,
(select area_id
from crm_area_country
WHERE area_type = 'office'
start with area_id = c.supplier_country_id
connect by prior area_parent_id = area_id) a,
(select area_id
from crm_area_country
WHERE area_type = 'Regional'
start with area_id = c.supplier_country_id
connect by prior area_parent_id = area_id) b
from crm_supplier_baseinfo c
where supplier_cooperate_id = 'LATENCY') tmp,
crm_popedom cac
where (tmp.a = cac.area_id or tmp.b = cac.area_id);