我有层次结构数据，其中每个记录属于一个父组，并且需要检索所有记录加上它们的父记录，但是如果它已经包含在初始非递归项中，则不希望复制记录。
例如，下面的查询返回由非递归项提供的前6条记录，然后递归项返回父项的后5条记录，但是父项中的3条记录(child_id 4528, 4539和4541)已经由初始非递归项返回，不应该重复。
我尝试使用DISTINCT，在WHERE子句中添加cs.id!=t.child_id(没有效果?)，在子查询中使用t(显然不可能)，但没有成功。
如何修改此查询以不返回重复行?
facdocs=> WITH RECURSIVE t(id, child_id, parent_id) AS (
SELECT s.id, cs.id child_id, cs.parent_id, cs.name, cs.spec
FROM project p
INNER JOIN specification s ON s.project_id=p.id
INNER JOIN csi_spec cs ON cs.id=s.csi_spec_id
WHERE p.id = 1
UNION
SELECT null id, cs.id child_id, cs.parent_id, cs.name, cs.spec
FROM t, csi_spec cs
WHERE cs.id=t.parent_id AND cs.id!=t.child_id AND cs.id
)
SELECT * FROM t;
id | child_id | parent_id | name | spec
----+----------+-----------+----------------------------------------------------+-------------
7 | 4543 | 4541 | Medium-Voltage, Single- and Multi-Conductor Cables | 26 05 13.16
8 | 4528 | 2 | Electrical | 26 00 00
9 | 4539 | 4528 | Common Work Results for Electrical | 26 05 00
11 | 4541 | 4539 | Medium-Voltage Cables | 26 05 13
12 | 4542 | 4541 | Medium-Voltage Open Conductors | 26 05 13.13
13 | 4578 | 4573 | Wiring Device Schedule | 26 06 20.26
| 4528 | 2 | Electrical | 26 00 00
| 4539 | 4528 | Common Work Results for Electrical | 26 05 00
| 4541 | 4539 | Medium-Voltage Cables | 26 05 13
| 4573 | 4571 | Schedules for Low-Voltage Electrical Distribution | 26 06 20
| 4571 | 4528 | Schedules for Electrical | 26 06 00
(11 rows)
以下表格仅供参考:
facdocs=> d csi_spec
Table "public.csi_spec"
Column | Type | Collation | Nullable | Default
-----------+------------------------+-----------+----------+---------
id | integer | | not null |
parent_id | integer | | not null |
name | character varying(255) | | not null |
spec | character varying(255) | | not null |
div | character varying(2) | | not null |
section | character varying(2) | | not null |
scope | character varying(2) | | not null |
subscope | character varying(2) | | not null |
Indexes:
"csi_spec_pkey" PRIMARY KEY, btree (id)
"idx_unique_spec" UNIQUE, btree (div, section, scope, subscope)
"uniq_92347c2dc00e173e" UNIQUE, btree (spec)
"idx_92347c2d727aca70" btree (parent_id)
"idx_div" btree (div)
"idx_scope" btree (subscope)
"idx_section" btree (section)
Foreign-key constraints:
"fk_92347c2d727aca70" FOREIGN KEY (parent_id) REFERENCES csi_spec(id)
Referenced by:
TABLE "csi_spec" CONSTRAINT "fk_92347c2d727aca70" FOREIGN KEY (parent_id) REFERENCES csi_spec(id)
TABLE "specification" CONSTRAINT "fk_e3f1a9af4ead25e" FOREIGN KEY (csi_spec_id) REFERENCES csi_spec(id)
facdocs=> d specification
Table "public.specification"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+---------
id | integer | | not null |
project_id | integer | | not null |
owner_id | integer | | not null |
csi_spec_id | integer | | not null |
create_at | timestamp(0) without time zone | | not null |
notes | text | | |
Indexes:
"specification_pkey" PRIMARY KEY, btree (id)
"idx_e3f1a9a166d1f9c" btree (project_id)
"idx_e3f1a9a7e3c61f9" btree (owner_id)
"idx_e3f1a9af4ead25e" btree (csi_spec_id)
Foreign-key constraints:
"fk_e3f1a9a166d1f9c" FOREIGN KEY (project_id) REFERENCES project(id)
"fk_e3f1a9a7e3c61f9" FOREIGN KEY (owner_id) REFERENCES "user"(id)
"fk_e3f1a9af4ead25e" FOREIGN KEY (csi_spec_id) REFERENCES csi_spec(id)
Referenced by:
TABLE "document" CONSTRAINT "fk_d8698a76908e2ffe" FOREIGN KEY (specification_id) REFERENCES specification(id)
facdocs=> d project
Table "public.project"
Column | Type | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+-----------------------------------
id | integer | | not null |
account_id | integer | | not null |
name | character varying(255) | | not null |
project_id | character varying(255) | | not null |
create_at | timestamp(0) without time zone | | not null |
is_active | boolean | | not null |
start_at | timestamp(0) without time zone | | | NULL::timestamp without time zone
description | text | | |
Indexes:
"project_pkey" PRIMARY KEY, btree (id)
"name_unique" UNIQUE, btree (name, account_id)
"project_id_unique" UNIQUE, btree (project_id, account_id)
"idx_2fb3d0ee9b6b5fba" btree (account_id)
Foreign-key constraints:
"fk_2fb3d0ee9b6b5fba" FOREIGN KEY (account_id) REFERENCES account(id)
Referenced by:
TABLE "project_vendor" CONSTRAINT "fk_e286d8bc166d1f9c" FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE
TABLE "specification" CONSTRAINT "fk_e3f1a9a166d1f9c" FOREIGN KEY (project_id) REFERENCES project(id)
facdocs=>
一个简单的解决方法是在主查询中排除重复项:
WITH RECURSIVE t (...)
SELECT DISTINCT ON (child_id) *
FROM t
ORDER BY child_id, id;
这应该可以工作，因为DISTINCT ON将返回每client_id和NULL排序的第一行在默认情况下结束。