计数内连接查询

Question

我有两个表，一个是“化身”，另一个是“孩子”--子表包含父化身ID和子化身ID的值。我试图从表中得到一个有两个以上孩子的化身的列表。

表：

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,
    mainParent NUMBER(1,0)
    );

关系：

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

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

我的问题是：

SELECT count(children.avatarid), avatars.username
FROM avatars
INNER JOIN (
  SELECT *
  FROM avatars
  INNER JOIN children ON avatars.avatarID = children.childAvatarID
  WHERE avatars.gender = 'M'
) children ON avatars.avatarID = children.parentavatarID
WHERE avatars.gender = 'M'
GROUP By children.avatarid, avatars.username;

它显示的计数(children.avatarid)总是1，无论一个化身有多少个孩子。

Answer 1

你是按children.avatarid, avatars.username分组，试着按avatars.username分组。

SELECT count(children.avatarid), avatars.username
FROM avatars
INNER JOIN (
  SELECT *
  FROM avatars
  INNER JOIN children ON avatars.avatarID = children.childAvatarID
  WHERE avatars.gender = 'M'
) children ON avatars.avatarID = children.parentavatarID
WHERE avatars.gender = 'M'
GROUP By avatars.username;

Answer 2

有两个以上孩子的化身名单

试试这个：

select a.*,
    c.child_count
from avatars a
join (
    select parentAvatarId,
        count(*) as child_count
    from children c
    join avatars a on a.avatarId = c.childAvatarId
    where a.gender = 'M'
    group by parentAvatarId
    having count(*) > 2
    ) c on a.avatarId = c.parentAvatarId
where a.gender = 'M'

Answer 3

select avatars.avatarID, count(children.childID) as numberOfChildren
from avatars 
join children on children.parentAvatarID = avatars.avatarID
group by children.parentAvatarID
having numberOfChildren >= 2;