SELECT父亲+他的孩子+按nPages排序的子书

user2241980 发表于 Dev

用户2241980

父亲表

id     name    age    married
9      Max     45     1

儿童桌

id    father_id    name 
12       9         Rose

书桌

id    owner_id   title            nPages
1        12       Harry potter    755
2        12       JDK             124

我尝试过

Select father.*, child.*, books.*
FROM fathers father
LEFT JOIN children child
    ON child.father_id = father.id
LEFT JOIN books book
    ON book.owner_id = child.id
WHERE father.married = 1
LIMIT 1

归还父子+只有一本书。但是我需要孩子拥有的所有书，按nPages排序。

像这样：

[
  name : MAX , 
  age : 45, 
  married : 1, 
  child => [
     father_id => 9, 
     name => Rose, 
     books => [
        book1 => ..., 
        book2 => ...
     ]

  ]
]

请你帮助我好吗？

注意！父亲只能生一个孩子。

你有没有尝试过这样的事情..

SELECT
    father.name AS fatherName,
    child.name AS childName,
    COUNT(books.owner_id) AS bookCount
FROM
    books
    INNER JOIN child ON books.owner_id = child.id
    INNER JOIN father ON child.father_id = father.id
GROUP BY
    books.owner_id
ORDER BY
    bookCount DESC
LIMIT 1

更新（未选中）

SELECT
    father.name AS fatherName,
    child.name AS childName,
    books.title AS bookName,
    books.nPages AS pageCount
FROM
    books
    INNER JOIN child ON books.owner_id = child.id
    INNER JOIN father ON child.father_id = father.id
WHERE 
    books.id IN
                (
                    SELECT
                          books.id
                    FROM 
                          books
                    GROUP BY
                          books.owner_id
                    ORDER BY
                          COUNT(books.owner_id) DESC
                    LIMIT 1
                )
ORDER BY
    books.nPages DESC

最终更新（未选中）

SELECT
    father.name AS fatherName,
    child.name AS childName,
    books.title AS bookName,
    books.nPages AS pageCount
FROM
    child
    INNER JOIN father ON child.father_id = father.id
    INNER JOIN books ON books.owner_id = child.id
WHERE 
    child.id IN
                (
                    SELECT
                          books.owner_id
                    FROM 
                          books
                    GROUP BY
                          books.owner_id
                    ORDER BY
                          COUNT(books.owner_id) DESC
                    LIMIT 1
                )
ORDER BY
    books.nPages DESC