- CREATE MATERIALIZED VIEW MV_SY_SHOP
- REFRESH FORCE ON DEMAND
- START WITH TO_DATE('07-09-2015', 'DD-MM-YYYY') NEXT TRUNC(SYSDATE+1)
- AS
- SELECT T1.SYDP_NAME AS SHOP,
- T1.SYDP_ID AS SHOP_ID,
- T2.SYDP_NAME AS CSHOP,
- T2.SYDP_ID AS CSHOP_ID,
- T3.SYDP_NAME AS DISTRICT,
- T3.SYDP_ID AS DISTRICT_ID,
- T4.SYDP_NAME AS REGION,
- T4.SYDP_ID AS REGION_ID ,
- T5.SYDP_NAME AS AREA,
- T5.SYDP_ID AS AREA_ID
- FROM
- (SELECT SYDP_ID, SYDP_PARENT_ID, SYDP_NAME
- FROM SY_DEPARTMENT T
- WHERE LEVEL = 5 AND T.SYDP_STATUS = 1
- START WITH (SYDP_ID = 'A16000' OR SYDP_ID = 'A16001')
- CONNECT BY PRIOR SYDP_ID = SYDP_PARENT_ID) t1,
- (SELECT SYDP_ID, SYDP_PARENT_ID, SYDP_NAME
- FROM SY_DEPARTMENT T
- WHERE LEVEL = 4 AND T.SYDP_STATUS = 1
- START WITH (SYDP_ID = 'A16000' OR SYDP_ID = 'A16001')
- CONNECT BY PRIOR SYDP_ID = SYDP_PARENT_ID) t2,
- (SELECT SYDP_ID, SYDP_PARENT_ID, SYDP_NAME
- FROM SY_DEPARTMENT T
- WHERE LEVEL = 3 AND T.SYDP_STATUS = 1
- START WITH (SYDP_ID = 'A16000' OR SYDP_ID = 'A16001')
- CONNECT BY PRIOR SYDP_ID = SYDP_PARENT_ID) t3,
- (SELECT SYDP_ID, SYDP_PARENT_ID, SYDP_NAME
- FROM SY_DEPARTMENT T
- WHERE LEVEL = 2 AND T.SYDP_STATUS = 1
- START WITH (SYDP_ID = 'A16000' OR SYDP_ID = 'A16001')
- CONNECT BY PRIOR SYDP_ID = SYDP_PARENT_ID) t4,
- (SELECT SYDP_ID, SYDP_PARENT_ID, SYDP_NAME
- FROM SY_DEPARTMENT T
- WHERE LEVEL = 1 AND T.SYDP_STATUS = 1
- START WITH (SYDP_ID = 'A16000' OR SYDP_ID = 'A16001')
- CONNECT BY PRIOR SYDP_ID = SYDP_PARENT_ID) t5
- WHERE T1.SYDP_PARENT_ID = T2.SYDP_ID
- AND t2.SYDP_PARENT_ID = T3.SYDP_ID
- AND t3.SYDP_PARENT_ID = T4.SYDP_ID
- AND t4.SYDP_PARENT_ID = T5.SYDP_ID;
- --该片段来自于http://www.codesnippet.cn/detail/0807201614826.html
来源: http://www.codesnippet.cn/detail/0807201614826.html