sql - PostgreSQL - 计算层次结构中的元素

Question

有一张 table :

CREATE TABLE product_categories (
id INT NOT NULL PRIMARY KEY,
parent INT NOT NULL,
name varchar(50) NOT NULL,
isProduct boolean NOT NULL
);

有什么方法可以计算每个类别中的产品数量吗？

即:

INSERT INTO product_categories VALUES (1, NULL, 'Main', 'no');

INSERT INTO product_categories VALUES (2, 1, 'Plant', 'no');
INSERT INTO product_categories VALUES (3, 2, 'Cactus', 'yes');
INSERT INTO product_categories VALUES (4, 2, 'Spruce', 'yes');
INSERT INTO product_categories VALUES (5, 2, 'Birch', 'yes');
INSERT INTO product_categories VALUES (6, 2, 'Pine', 'yes');

INSERT INTO product_categories VALUES (7, 1, 'Stock', 'no');
INSERT INTO product_categories VALUES (8, 7, 'Spade', 'yes');
INSERT INTO product_categories VALUES (9, 7, 'Watering can', 'yes');

并且应该收到:

Category | Count
Main     | 6
Plant    | 4
Stock    | 2

Answer 1

您需要使用 Recursive Common Table Expression

WITH RECURSIVE Parents AS
(   SELECT  ID, Parent, Name, IsProduct
    FROM    product_categories
    WHERE   Parent IS NOT NULL
    UNION ALL
    SELECT  c.ID, p.Parent, c.Name, c.IsProduct
    FROM    product_categories c
            INNER JOIN Parents p
                ON p.ID = c.Parent
)
SELECT  pc.Name, 
        COUNT(*) AS Products, 
        ARRAY_AGG(p.Name) AS ProductList
FROM    product_categories pc
        INNER JOIN Parents p
            ON p.Parent = pc.ID
WHERE   p.IsProduct = 'yes'
GROUP BY pc.Name

Working Example