postgresql – Postgres计数与自引用连接条件
发布时间:2020-12-13 16:09:05 所属栏目:百科 来源:网络整理
导读:鉴于以下结构 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是一种回归产品的自我参照关系. 我基本上试图计运
鉴于以下结构
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 name 1 subcategory_1 2 subcategory_2 3 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 子类别ID 2 子类别3 更新 删除了额外的可能令人困惑的列,添加了示例数据和输出 解决方法
如果引用的最大深度是一个级别,那么这个简单的查询就完成了这项工作:
select subcategory_id,name,count(*) from ( select distinct subcategory_id,coalesce(stack_id,id) stack_id from products ) sub join subcategories s on s.id = sub.subcategory_id group by 1,2 order 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 products union 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,stack from pr order 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 ) sub join subcategories s on s.id = sub.subcategory_id group by 1,2 subcategory_id | name | count ----------------+---------------+------- 1 | subcategory_1 | 1 2 | subcategory_2 | 3 3 | subcategory_3 | 2 (3 rows) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |