必须将relation与people的2个副本联接。
第一个副本将返回孩子的名字，第二个副本将返回父母的名字。
然后按子级分组并使用条件聚合在一行中获取父级的名称：

SELECT c.name Child, 
       MAX(CASE WHEN p.gender = 'F' THEN p.name END) Mother, 
       MAX(CASE WHEN p.gender = 'M' THEN p.name END) Father
FROM relation r
INNER JOIN people c ON c.id = r.cid
INNER JOIN people p ON p.id = r.pid
GROUP BY r.cid, c.name;

请参见demo。

您可以加入两次，然后PIVOT：

SELECT *
FROM   (
  SELECT c.name AS child,
         p.name AS parent,
         p.gender
  FROM   relations r
         INNER JOIN people c
         ON r.cid = c.id
         INNER JOIN people p
        ON r.pid = p.id
)
PIVOT (
  MAX(parent) FOR gender IN (
    'M' AS father,
    'F' AS mother
  )
)

其中，对于示例数据：

CREATE TABLE people (id, name, gender) AS
SELECT 101, 'Riya',   'F' FROM DUAL UNION ALL
SELECT 566, 'Aman',   'M' FROM DUAL UNION ALL
SELECT 202, 'Rakesh', 'M' FROM DUAL UNION ALL
SELECT 875, 'lucky',  'M' FROM DUAL UNION ALL
SELECT 202, 'Reena',  'F' FROM DUAL UNION ALL
SELECT 322, 'Raina',  'F' FROM DUAL UNION ALL
SELECT 345, 'Rohit',  'M' FROM DUAL UNION ALL
SELECT 322, 'Mohit',  'M' FROM DUAL UNION ALL
SELECT 345, 'Meena',  'F' FROM DUAL;

CREATE TABLE relations (cid, pid) AS
SELECT 101, 202 FROM DUAL UNION ALL
SELECT 566, 322 FROM DUAL UNION ALL
SELECT 875, 345 FROM DUAL;

输出：
| 儿童|父亲|母亲|
| - ------|- ------|- ------|
| 里亚|拉凯什|雷纳|
| 阿曼|莫希特|赖纳|
| 幸运|罗希特|米纳|

• db〈〉小提琴here *

试试这个

with cte as
(
 select name as child, r.pid as pid 
   from people p
   join relation r
     on p.id=r.cid
)
select ee.child as child, 
       (select name from people PM where id=ee.pid and PM.gender='F') as Mother,
       (select name from people PP where id=ee.pid and PP.gender='M') as Father 
  from cte ee

您可以尝试以下查询-

SELECT P.name Child, p2.name mother, p3.name father
  FROM relation R
  JOIN people P ON R.cid = P.id
  JOIN people P2 ON R.pid = P.id
                AND P.gender = 'F'
  JOIN people P3 ON R.pid = P.id
                AND P.gender = 'M'

SELECT DISTINCT P1.NAME AS CHILD,(SELECT NAME FROM PEOPLE PM WHERE ID=T.PID AND PM.GENDER='F') AS MOTHER,
       (SELECT NAME FROM PEOPLE PP WHERE ID=T.PID AND PP.GENDER='M') AS FATHER FROM 
(SELECT R.CID,NAME,R.PID,P.GENDER FROM PEOPLE P,RELATIONS R
WHERE P.ID=R.PID) T,PEOPLE P1
WHERE T.CID=P1.ID;

选择 * 自（选择 * 自（选择p.名作为子项，（从人员p1中选择p1.名，其中r.p_id=p1.id且p1.gender=“M”）作为父项从人员p内部连接关系rp.idr.id）t
其中t.father不为空）作为table1内部连接
（选择 * 从（选择p.名作为子项，（从人员p1中选择p1.名，其中r.p_id=p1.id且p1.gender =“F”）作为母亲从人员p中选择内部连接关系r，网址为p.id=r.id）t
其中T.Mother不为空）如表2
在表1.child上=表2.child

SELECT l.cid       AS id,
           r.NAME      AS Child,
           mother.NAME AS Mother,
           father.NAME AS Father
    FROM   testdb.dbo.people r
           JOIN testdb.dbo.relation l
             ON l.cid = r.id
           JOIN (SELECT l.cid AS id,
                        r.NAME
                 FROM   testdb.dbo.people r
                        JOIN testdb.dbo.relation l
                          ON l.pid = r.id
                             AND gender = 'F') AS mother
             ON l.cid = mother.id
           JOIN (SELECT l.cid AS id,
                        r.NAME
                 FROM   testdb.dbo.people r
                        JOIN testdb.dbo.relation l
                          ON l.pid = r.id
                             AND gender = 'M') AS father
             ON l.cid = father.id

按子级名称的顺序分别在各个列中打印子级及其父级名称的查询。

SELECT c.name as child, 
    MAX(CASE WHEN p.gender = 'F' THEN p.name END) AS mother,
    MAX(CASE WHEN p.gender = 'M' THEN p.name END) AS father
FROM relations r
INNER JOIN public."People" c on c.id = r.cid
INNER JOIN public."People" p on p.id = r.pid
GROUP BY r.cid, c.name ORDER BY c.name;