如何写总分结构数据统计查询语句

我的数据库是Oracle 11g
现在在编写一个查询语句，遇到这个表不知道该如何查询，需要帮助。

表结构及部分内容如下

A表分类表

B表子分类业务表

现在需求是需要统计出每个总类的库存数量，这个查询难点是在于子类PK与总类PK的关系，并非是设计成多行，而是在单行的一个字段内，采用逗号分隔出了下级子类的PK。

请问这个SQL查询应该怎么写？

为了更好的测试结果，你最好把建表的语句也发出来方便核实

SELECT SUBSTR(SYS_CONNECT_BY_PATH(hierarchy, ','), 2) AS total_class,
       SUM(stock_quantity) AS total_stock
FROM (
  SELECT h.total_class AS hierarchy,
         b.stock_quantity
  FROM (
    SELECT DISTINCT TRIM(regexp_substr(total_class, '[^,]+', 1, levels.column_value)) AS total_class
    FROM (
      SELECT total_class,
             ROW_NUMBER() OVER(PARTITION BY total_class ORDER BY total_class) AS rn
      FROM (
        SELECT a.total_class || ',' || a.sub_class AS total_class
        FROM (
          SELECT total_class,
                 TRIM(REGEXP_SUBSTR(sub_class, '[^,]+', 1, levels.column_value)) AS sub_class
          FROM (
            SELECT 'A' AS total_class, 'A1,A2,A3,A4' AS sub_class FROM dual UNION ALL
            SELECT 'B' AS total_class, 'B1,B3,B4' AS sub_class FROM dual UNION ALL
            SELECT 'C' AS total_class, 'C1,C2' AS sub_class FROM dual UNION ALL
            SELECT 'D' AS total_class, 'D1,D4,D5' AS sub_class FROM dual
          ) t
          CONNECT BY REGEXP_SUBSTR(sub_class, '[^,]+', 1, levels.column_value) IS NOT NULL
        ) a
        CONNECT BY REGEXP_SUBSTR(sub_class, '[^,]+', 1, levels.column_value) IS NOT NULL
      ) t
      WHERE rn = 1
    ) h
    LEFT JOIN B_TABLE b ON h.total_class = b.sub_class
)
START WITH hierarchy IS NULL
CONNECT BY PRIOR total_class = hierarchy
GROUP BY hierarchy;

MySQL可以使用FIND_IN_SET函数

SELECT 
    A.总类PK, 
    SUM(B.库存数量) AS 库存数量
FROM 
    A表分类表 A 
    JOIN B表子分类业务表 B ON FIND_IN_SET(B.子类PK, A.子类PK) > 0
GROUP BY 
    A.总类PK;

Oracle也有平替，参考：https://blog.51cto.com/xiaok007/2139728

• 这个问题的回答你可以参考下: https://ask.csdn.net/questions/195706