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

Question

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

表结构及部分内容如下

A表分类表

总类PK	子类PK
A	A1,A2,A3,A4
B	B1,B3,B4
C	C1,C2
D	D1,D4,D5

B表子分类业务表

子类PK	库存数量
A1	12300
A3	1000
A4	500
B3	150000
B4	13000
C1	100000

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

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

Answer 1

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

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;