从内部联接表中提取数据并将两行合并为一列

Question

我想要从查询中实现的是选择所有的化身及其父亲和母亲(通过获取parentID和性别来确定)，并为每个化身显示1行头像用户名、父亲用户名、母亲用户名。

这是一种解决方案，我考虑的是没有完成返回第一个父亲的子查询和返回母亲的内部连接的另一个子查询。

SELECT avatars.username as ChildUsername, father.username as FatherUsername, mother.username as MotherUsername
FROM avatars
INNER JOIN avatars father(
  SELECT *
  FROM children
  INNER JOIN children child ON 
  WHERE avatars.gender = 'M' AND 
)
INNER JOIN avatars mother

数据库：

CREATE TABLE avatars
    (avatarID NUMBER CONSTRAINT pk_avatars PRIMARY KEY,
    username VARCHAR2(20),
    gender CHAR(1)
    );

CREATE TABLE children
    (childID NUMBER CONSTRAINT pk_children PRIMARY KEY,
    childAvatarID NUMBER,
    parentAvatarID NUMBER
    );

ALTER TABLE children
ADD FOREIGN KEY (parentAvatarID)
REFERENCES avatars(avatarID);

ALTER TABLE children
ADD FOREIGN KEY (childAvatarID)
REFERENCES avatars(avatarID);

数据：

INSERT INTO avatars VALUES (1,'av1','M');
INSERT INTO avatars VALUES (2,'av2','F');
INSERT INTO avatars VALUES (3,'av3','M');

INSERT INTO children VALUES (1,3,1); //Father 
INSERT INTO children VALUES (2,3,2); //Mother

对于上面的数据，我希望得到这个输出

   ChildUsername FatherUsername MotherUsername
   av3           av1            av2

Answer 1

你需要两次加入阿凡达的桌子-自我和父母。

select s.username as ChildUsername,
    max(case when p.gender = 'M' then p.username end) as FatherUsername,
    max(case when p.gender = 'F' then p.username end) as MotherUsername,
    max(case when p.gender = 'M' then p.someothercolumn end) as FathersSomeOtherColumn,
    max(case when p.gender = 'F' then p.someothercolumn end) as MothersSomeOtherColumn,
    . . .
from children c
join avatars s on c.childAvatarID = s.avatarId
left join avatars p on c.parentAvatarId = p.avatarId
group by c.childAvatarID,
    s.username;

Answer 2

使用条件聚合：

select 
    av.username
  , max(case when p.gender = 'M' then p.username end) as Father
  , max(case when p.gender = 'F' then p.username end) as Mother
from avatars av
  inner join children c
    on av.avatarid = c.childavatarid
  left join avatars p
    on p.avatarid = c.parentavatarid
group by av.username

Answer 3

我相信这将有效地选择父母作为子查询。

SELECT distinct a.username as ChildUsername, f.FatherUsername, m.MotherUsername
FROM avatars a
INNER JOIN (
  SELECT a.username as FatherUsername, c.childAvatarID
  FROM avatars a
  INNER JOIN children c 
  ON a.avatarID = c.parentAvatarID
  WHERE a.gender = 'M'
) f
ON a.avatarID = f.childAvatarID
INNER JOIN (
  SELECT a.username as MotherUsername, c.childAvatarID
  FROM avatars a
  INNER JOIN children c 
  ON a.avatarID = c.parentAvatarID
  WHERE a.gender = 'F'
) m
ON a.avatarID = m.childAvatarID