Как получить список родных шагов в postgres?
Ниже приведена моя схема:
postgres=# \d check_user;
Table "public.check_user"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
id | integer | not null default nextval('check_user_id_seq'::regclass)
name | text |
gender | text |
Indexes:
"check_user_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "parent" CONSTRAINT "parent_left_node_fkey" FOREIGN KEY (left_node) REFERENCES check_user(id)
TABLE "parent" CONSTRAINT "parent_right_node_fkey" FOREIGN KEY (right_node) REFERENCES check_user(id)
TABLE "spouse" CONSTRAINT "spouse_husband_fkey" FOREIGN KEY (husband) REFERENCES check_user(id)
TABLE "spouse" CONSTRAINT "spouse_wife_fkey" FOREIGN KEY (wife) REFERENCES check_user(id)
postgres=# \d parent;
Table "public.parent"
Column | Type | Modifiers
------------+---------+-----------------------------------------------------
id | integer | not null default nextval('parent_id_seq'::regclass)
left_node | integer |
right_node | integer |
Indexes:
"parent_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"parent_left_node_fkey" FOREIGN KEY (left_node) REFERENCES check_user(id)
"parent_right_node_fkey" FOREIGN KEY (right_node) REFERENCES check_user(id)
postgres=# \d spouse
Table "public.spouse"
Column | Type | Modifiers
---------+---------+-----------------------------------------------------
id | integer | not null default nextval('spouse_id_seq'::regclass)
husband | integer |
wife | integer |
Indexes:
"spouse_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"spouse_husband_fkey" FOREIGN KEY (husband) REFERENCES check_user(id)
"spouse_wife_fkey" FOREIGN KEY (wife) REFERENCES check_user(id)
Ниже приведены данные в каждой из этих таблиц:
postgres=# select * from check_user;
id | name | gender
----+------+--------
4 | a | m
1 | x | m
2 | y | f
3 | z | f
7 | c3 | f
6 | c2 | m
5 | c1 | m
8 | b | m
9 | c4 | f
(9 rows)
postgres=# select * from spouse;
id | husband | wife
----+---------+------
1 | 1 | 2
2 | 1 | 3
3 | 4 | 2
4 | 8 | 3
(4 rows)
postgres=# select * from parent;
id | left_node | right_node
----+-----------+------------
1 | 1 | 5
2 | 2 | 5
3 | 1 | 6
4 | 3 | 6
5 | 4 | 7
6 | 2 | 7
7 | 8 | 9
8 | 3 | 9
Теперь я хочу найти родных шагов пользователя c1. Под сводными братьями и сестрами я имею в виду пользователей, которые являются сыном любой матери отца c1, но не обоих.
Я использую следующий запрос:-
with user_parents as (
select child.id as child_id, child.name as child_name, parent_dtls.id as parent_id,
parent_dtls.name as parent_name, child.gender as child_gender, parent_dtls.gender as parent_gender
from check_user child
inner join parent on child.id=parent.right_node
inner join check_user parent_dtls on parent.left_node=parent_dtls.id
),
fathers as (select * from user_parents where user_parents.parent_gender='m'),
mothers as (select * from user_parents where user_parents.parent_gender='f'),
my_father as (select * from fathers where fathers.child_id=5),
my_mother as (select * from mothers where mothers.child_id=5)
select user_.id as "user_id", fathers.parent_id as "father_id", my_father.parent_id "my_father_id",
mothers.parent_id as "mother_id", my_mother.parent_id as "my_mother_id" from parent parent_dtls
left join my_father on parent_dtls.left_node=my_father.parent_id
left join my_mother on parent_dtls.left_node=my_mother.parent_id
inner join check_user user_ on user_.id=parent_dtls.right_node
left join fathers on parent_dtls.right_node=fathers.child_id
left join mothers on parent_dtls.right_node=mothers.child_id where my_father.parent_id is null and my_mother.parent_id is null;
Но я получаю другие строки вместе со сводными братьями.
Что не так делаю? Более того, пожалуйста, прокомментируйте неэффективность, если она есть в запросе выше.
Заранее спасибо.
1 ответ
Я предпочитаю решать задачу шаг за шагом. Итак, код выглядит примерно так:
//select the child id
with child_id as (
select id from check_user where name = 'c1'),
//select parents ids
parents as (
select left_node, right_node from parent
where id in (select * from child_id)
)
select * from check_user where id in (
select id from parent where
//child of one parents, not both
(left_node in
(select left_node from parents)
and right_node not in
(select right_node from parents))
or (left_node not in (select left_node from parents)
and right_node in (select right_node from parents)
)
)
ps Похоже, таблица супругов не нужна в соответствии с описанием задачи.