mysql - SELECT父亲+他的 child +按nPages排序的 child 的书

父亲表

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

只归还一本书。但我需要孩子所有的书按页码分类。
像这样的：

[
  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

关于mysql - SELECT父亲+他的 child +按nPages排序的 child 的书，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19147768/