postgresql – Postgres计数与自引用连接条件

概述鉴于以下结构 CREATE TABLE products ( id integer NOT NULL, subcategory_id integer, stack_id integer,)CREATE TABLE subcategories ( id integer NOT NULL, name character varying(255)) product @H_301_2@ 鉴于以下结构

CREATE table products (    ID integer NOT NulL,subcategory_ID integer,stack_ID integer,)CREATE table subcategorIEs (  ID integer NOT NulL,name character varying(255))

products.stack_ID是一种回归产品的自我参照关系.

我基本上试图计运算符类别加入产品

products.subcategory_ID = subcategorIEs.ID

但是每个不同的堆栈组将计数限制为一次.

样本子类别表

ID    name1     subcategory_12     subcategory_23     subcategory_3

样品表

ID    subcategory_ID    stack_ID    1     1                 NulL        2     1                 1           3     2                 1           4     3                 1           5     2                 NulL        6     2                 5           7     2                 5           8     2                 NulL        9     3                 8           10    3                 8

样本所需的输出

ID    name             total 1     subcategory_1    1     (row 1)2     subcategory_2    3     (row 1 + row 5 + row 8)3     subcategory_3    2     (row 1 + 8)

输出说明

子类别ID 1
如果我与产品进行简单的连接,我会得到产品(1,2).我只想要不同父对象的数量(stack_ID为null),因此1个计数和2个已经计数的引用1因此不会增加计数.

子类别ID 2
加入将是(3,5,6,7,8). 3的stack_ID是1所以它计数1.产品5,6和7参考5,因此计数1.产品8计数1.

子类别3
加入是(4,9,10). 4参考文献1,9和10都参考8.

更新

删除了额外的可能令人困惑的列,添加了示例数据和输出

解决方法 如果引用的最大深度是一个级别,那么这个简单的查询就完成了这项工作：

select subcategory_ID,name,count(*)from (    select distinct subcategory_ID,coalesce(stack_ID,ID) stack_ID    from products    ) subjoin subcategorIEs s on s.ID = sub.subcategory_IDgroup by 1,2order by 1,2; subcategory_ID |     name      | count ----------------+---------------+-------              1 | subcategory_1 |     1              2 | subcategory_2 |     3              3 | subcategory_3 |     2(3 rows)

此递归查询也适用于深度超过一个级别的引用：

with recursive pr(ID,subcategory_ID,stack_ID,stack) as (    select ID,array[ID]    from productsunion    select pr.ID,pr.subcategory_ID,products.stack_ID,pr.stack_ID || pr.stack    from pr    join products on pr.stack_ID = products.ID    )select distinct on (ID) ID,stackfrom prorder by ID,array_length(stack,1) desc ID | subcategory_ID | stack  ----+----------------+--------  1 |              1 | {1}  2 |              1 | {1,2}  3 |              2 | {1,3}  4 |              3 | {1,4}  5 |              2 | {5}  6 |              2 | {5,6}  7 |              2 | {5,7}  8 |              2 | {8}  9 |              3 | {8,9} 10 |              3 | {8,10}(10 rows)

使用上述数据集加入子类别：

select subcategory_ID,stack[1]    from (        with recursive pr(ID,stack) as (            select ID,array[ID]            from products        union            select pr.ID,pr.stack_ID || pr.stack            from pr            join products on pr.stack_ID = products.ID            )        select distinct on (ID) ID,stack        from pr        order by ID,1) desc        ) sub    ) subjoin subcategorIEs s on s.ID = sub.subcategory_IDgroup by 1,2 subcategory_ID |     name      | count ----------------+---------------+-------              1 | subcategory_1 |     1              2 | subcategory_2 |     3              3 | subcategory_3 |     2(3 rows)

@H_301_2@ 总结

以上是内存溢出为你收集整理的postgresql – Postgres计数与自引用连接条件全部内容，希望文章能够帮你解决postgresql – Postgres计数与自引用连接条件所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。