MySQL在一个查询中有一对多的关系

How can I get data one to many relationship in ONE query?

如何在一个查询中获取一对多关系数据?

Example: one book has many authors.

示例:一本书有很多作者。

The result return should be like this:

结果返回应该是这样的:

array(
   'book_title' => 'HTML for Dummies',
   'book_desc'  => '...',
   'authors' => array(
       [0] => array(
           'name' => 'Someone',
           'dob'  => '...'
       ),
       [1] => array(
           'name' => 'Someone',
           'dob'  => '...'
       )
    )
)

I have tried using subquery to select the result but not luck:

我尝试使用子查询来选择结果但不是运气:

SELECT *, (
    SELECT * 
    FROM authors
    WHERE book_id = b.id
) AS authors
FROM book b;

Mysql error "Operand should contain 1 column(s)" which means that I only can select one column.

Mysql错误“操作数应包含1列”,这意味着我只能选择一列。

You may suggest me using join but how it can archive the return result format like I have shown you?

您可能会建议我使用联接,但它如何归档返回结果格式,就像我向您展示的那样?

4 个解决方案

#1

SELECT 
 B.id AS book_id, B.name AS book_name,
 A.name AS author_name ,A.ID AS author_id 
FROM book B
LEFT JOIN 
 author A ON B.id = A.book_id;

Refer : http://sqlfiddle.com/#!9/633cfa1/2

请参阅:http://sqlfiddle.com/#!9/633cfa1/2

Your output will be in numeric array.

您的输出将是数字数组。

Array
(
    [0] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorId] => 1
            [authorName] => Author 1
        )

    [1] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorId] => 2
            [authorName] => Author 2
        )

    [2] => Array
        (
            [bookId] => 2
            [bookName] => Book 2
            [authorId] => 3
            [authorName] => Author 3
        )

    [3] => Array
        (
            [bookId] => 3
            [bookName] => Book 3
            [authorId] => 4
            [authorName] => Author 4
        )

)

Convert this array to multideimentional array

将此数组转换为多维数组

Array
(
    [0] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorName] => Array
                (
                    [0] => Author 1
                    [1] => Author 2
                )

        )

    [1] => Array
        (
            [bookId] => 2
            [bookName] => Book 2
            [authorName] => Array
                (
                    [2] => Author 3
                )

        )

    [2] => Array
        (
            [bookId] => 3
            [bookName] => Book 3
            [authorName] => Array
                (
                    [3] => Author 4
                )

        )

)

Refer : https://3v4l.org/q1dli

请参阅:https://3v4l.org/q1dli

#2

SELECT b.*, a.*
FROM book b
INNER JOIN authors a ON (a.book_id = b.id);

#3

A Way that you want data might not be easy from mysql. Mysql will return as row wise. so Either books or author will repeat in result set. and you have to parse mysql result accordingly to prepare you array.

从mysql中获取数据可能并不容易。 Mysql将以行方式返回。所以书籍或作者都会在结果集中重复。并且你必须相应地解析mysql结果以准备你的数组。

Try following:

1) All books with author as comma separated ids.

1)所有以作者为逗号分隔ID的书籍。

"SELECT B.*,group_concat(a.id) as authors 
 From book b inner join authors as a on b.author_id=a.id 
 group by b.id;"

2) All authors with book details:

2)所有作者都有书的详细信息:

"SELECT A.*,group_concat(B.id) as books 
 From book B inner join authors as A on B.author_id=A.id 
 group by A.id;"

#4

If you want to do it quick and simple, use GROUP_CONCAT with an appropriate delimiter character. Then split the resulting concatenated string field in your PHP application (or whatever language you're using) to quickly get the inner array you want.

如果您想快速而简单地使用它,请使用具有适当分隔符的GROUP_CONCAT。然后在PHP应用程序(或您正在使用的任何语言)中分割生成的连接字符串字段,以快速获取所需的内部数组。

This has the advantage that you will only have one row per book in the output.

这样做的好处是输出中每本书只有一行。

#1

SELECT 
 B.id AS book_id, B.name AS book_name,
 A.name AS author_name ,A.ID AS author_id 
FROM book B
LEFT JOIN 
 author A ON B.id = A.book_id;