使用parent_category_id的产品计数

Question

我有两个表1: cat_a

​

​

表2:prod_a

​

​

输出必须是

When category_id =1 then product_id count must be 7 
when category_id = 3 then product_id count must be 5
when category_id = 6 then product_id count must be 3
when category_id = 7 then product_id count must be 2
when category_id = 5 then product_id count must be 2

请帮帮我。我已经搜索了很多问题和答案以及论坛。找不到确切的解决方案。我不想要填鸭式的答案。请注明提示或带答案的方法。

Answer 1

我的问题解决了。

SELECT count( product_id)
FROM prod_A 
WHERE  category_id IN (SELECT category_id FROM cat_A  START WITH category_id = <i_category_id>
     CONNECT BY PRIOR category_id = parent_catogory);