如何在MySql中选择以下内容？

Question

我有一个用户系统，其中用户帐户可以拥有从属帐户。

为了表达这种关系，我有以下mysql表：

TABLE ACCOUNTS
ID
name

TABLE OWNEDACCOUNTS
accountID -> ACCOUNTS(ID)
ownerID -> ACCOUNTS(ID)

尽管从这些表中看不出来，但这种关系只能深入两个层次：

Account ->子账号->子账号

目前，我可以选择属于顶级帐户的所有子帐户，具体如下：

SELECT  a.ID
FROM    accounts AS a
JOIN    ownedAccounts AS o
ON      o.accountID = a.ID
        AND o.ownerID = ?

但是，我真正想要的是选择所有子帐户和属于单个顶级帐户的所有子子帐户。我该怎么做呢？

(提前)感谢你的帮助

Answer 1

Select ...
From accounts AS a
    Join ownedAccounts AS o
        On o.accountID = a.ID
    Join accounts AS owner1
        On owner1.ID = o.ownerID
    Left Join ownedAccounts AS o2
        On o2.accountID = owner1.ID
    Left Join accounts As owner2
        On owner2.ID = o2.ownerID
Where o.ownerID = ?

Answer 2

这应该列出你拥有的每个帐户，以及它的sub和sub sub的计数。希望这是一个很好的起点。

SELECT  a.ID, count(sub.accountID), count(subsub.accountID)
FROM    accounts AS a
LEFT JOIN    ownedAccounts AS sub ON      sub.ownerID = a.ID
LEFT JOIN    ownedAccounts AS subsub ON      subsub.ownerID = sub.accountID