MySQL Select/Join 仅返回第一行

Question

我有这个 MySQL 查询从数据库返回一些数据，但由于某种原因，它只返回第一行，我可能做错了什么？

SELECT `a`.`id` AS topic_id, `a`.`text` AS topic_text, 
 `a`.`date` AS topic_date, `b`.`name` AS author_name, 
 `b`.`picture` AS author_picture, `b`.`facebook_id` AS author_facebook_id, 
SUM(CASE WHEN c.topic_id IS NOT NULL THEN 1 ELSE 0 END) AS topic_likes_number, 
SUM(CASE WHEN c.topic_id IS NOT NULL AND c.author_id = 1 THEN 1 ELSE 0 END) AS topic_liked, 
SUM(CASE WHEN d.topic_id IS NOT NULL THEN 1 ELSE 0 END) AS topic_dislikes_number, 
SUM(CASE WHEN d.topic_id IS NOT NULL AND c.author_id = 1 THEN 1 ELSE 0 END) AS topic_disliked, 
SUM(CASE WHEN e.topic_id IS NOT NULL THEN 1 ELSE 0 END) AS topic_comments_number
    FROM (`topics` AS a)
    INNER JOIN `users` AS b ON `b`.`id` = `a`.`author_id`
    LEFT JOIN `topics_likes` AS c ON `c`.`topic_id` = `a`.`id`
    LEFT JOIN `topics_dislikes` AS d ON `c`.`topic_id` = `a`.`id`
    LEFT JOIN `comments` AS e ON `e`.`topic_id` = `a`.`id`
    LIMIT 50

这就是我所期待的(多个主题):

Array
(
    [0] => Array
        (
            [topic_id] => 1
            [topic_text] => Donec id elit non mi porta gravida at eget metus. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Etiam porta sem malesuada magna mollis euismod. Donec sed odio dui.
            [topic_date] => 0000-00-00 00:00:00
            [author_name] => Claudius Ibn
            [author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
            [author_facebook_id] => 100003268692398
            [topic_likes_number] => 1
            [topic_liked] => 1
            [topic_dislikes_number] => 0
            [topic_disliked] => 0
            [topic_comments_number] => 0
        )
    [1] => Array
        (
            [topic_id] => 2
            [topic_text] => Donec id elit non mi porta gravida at eget metus. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Etiam porta sem malesuada magna mollis euismod. Donec sed odio dui.
            [topic_date] => 0000-00-00 00:00:00
            [author_name] => Claudius Ibn
            [author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
            [author_facebook_id] => 100003268692398
            [topic_likes_number] => 1
            [topic_liked] => 1
            [topic_dislikes_number] => 0
            [topic_disliked] => 0
            [topic_comments_number] => 0
        )
    [2] => Array
        (
            [topic_id] => 3
            [topic_text] => Donec id elit non mi porta gravida at eget metus. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Etiam porta sem malesuada magna mollis euismod. Donec sed odio dui.
            [topic_date] => 0000-00-00 00:00:00
            [author_name] => Claudius Ibn
            [author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
            [author_facebook_id] => 100003268692398
            [topic_likes_number] => 1
            [topic_liked] => 1
            [topic_dislikes_number] => 0
            [topic_disliked] => 0
            [topic_comments_number] => 0
        )
    [...]
)

这就是我得到的(一个主题):

Array
(
    [0] => Array
        (
            [topic_id] => 1
            [topic_text] => Donec id elit non mi porta gravida at eget metus. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Etiam porta sem malesuada magna mollis euismod. Donec sed odio dui.
            [topic_date] => 0000-00-00 00:00:00
            [author_name] => Claudius Ibn
            [author_picture] => 356a192b7913b04c54574d18c28d46e6395428ab.jpg
            [author_facebook_id] => 100003268692398
            [topic_likes_number] => 1
            [topic_liked] => 1
            [topic_dislikes_number] => 0
            [topic_disliked] => 0
            [topic_comments_number] => 0
        )

)

解决方案:只需添加一个group_by a.id

Answer 1

这行似乎是错误的，即使这可能不是您的问题:

LEFT JOIN `topics_dislikes` AS d ON `c`.`topic_id` = `a`.`id`