刷新物化视图和查看子类四级分类

来源：互联网发布：java实现员工管理系统编辑：程序博客网时间：2024/05/21 14:06

exec dbms_refresh.refresh('mv_goods_list')
   ( SELECT C2.CATGROUP_ID_CHILD G4
          FROM xiu_goods.CATGRPREL C
          LEFT JOIN xiu_goods.CATGRPREL C1
            ON C1.CATGROUP_ID_PARENT = C.CATGROUP_ID_CHILD
           AND C1.CATALOG_ID = C.CATALOG_ID
          LEFT JOIN xiu_goods.CATGRPREL C2
            ON c2.CATGROUP_ID_PARENT = C1.CATGROUP_ID_CHILD
           AND C2.CATALOG_ID = C1.CATALOG_ID
         WHERE C.CATALOG_ID = 10001
           AND C.CATGROUP_ID_PARENT = 14715 --查询四级分类
           AND C2.CATGROUP_ID_CHILD IS NOT NULL)
ORDER BY CES.LASTUPDATE DESC
--查询父类下所有四级分类
SELECT C2.CATGROUP_ID_CHILD G4
                           FROM xiu_goods.CATGRPREL C
                           LEFT JOIN xiu_goods.CATGRPREL C1
                             ON C1.CATGROUP_ID_PARENT = C.CATGROUP_ID_CHILD
                            AND C1.CATALOG_ID = C.CATALOG_ID
                           LEFT JOIN xiu_goods.CATGRPREL C2
                             ON c2.CATGROUP_ID_PARENT = C1.CATGROUP_ID_CHILD
                            AND C2.CATALOG_ID = C1.CATALOG_ID
                          WHERE C.CATALOG_ID = 10001
                            AND C.CATGROUP_ID_CHILD = 14715
                            AND C2.CATGROUP_ID_CHILD IS NOT NULL