我有层次结构数据，其中每个记录都属于一个父组，并且需要检索所有记录及其父记录，但是如果初始非递归术语中已经包含该记录，则不希望重复该记录。
例如，以下查询返回非递归项所提供的前六个记录，然后递归项返回父项的下五个记录，但是父项记录中的三个（child_id 4528、4539和4541）已经由初始非递归项返回，并且不应重复。
我尝试使用DISTINCT，将cs.id!=t.child_idWHERE子句添加（没有效果？），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将返回per的第一行，client_id并在末尾返回NULL排序。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句