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

最新推荐文章于 2024-04-24 18:54:12 发布

原创最新推荐文章于 2024-04-24 18:54:12 发布 · 576 阅读

0 ·

CC 4.0 BY-SA版权

数据库Orcale Mysql 专栏收录该内容

44 篇文章

订阅专栏

此博客展示了如何使用SQL查询来获取特定分类目录下的所有四级子分类，涉及多表连接和条件筛选。

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