如何更改此SQL,以便我可以从每个作者那里获得一个帖子?
在下面的SQL中,我有最后5个帖子。但有时这些帖子出自同一作者之手。我想为每位作者选择最后5个,但只选择一个。
SELECT `e` . * ,
`f`.`title` AS `feedTitle` ,
`f`.`url` AS `feedUrl` ,
`a`.`id` AS `authorId` ,
`a`.`name` AS `authorName` ,
`a`.`about` AS `authorAbout`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categories` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categories` AS `c` ON ec.category_id = c.id
INNER JOIN `authors` AS `a` ON e.author_id = a.id
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
LIMIT 5
编辑
我最终得到了它:
SELECT a.id, e.date, e.title, a.name
FROM feed_entries e, authors a
WHERE e.author_id =a.id
ORDER BY e.date DESC
LIMIT 5
在此查询中,如何才能为每位作者只获得一篇帖子?
解决方案
我这里有答案:DISTINCT a column in a database
我做了一些修改,工作正常。
SELECT * FROM (
SELECT `e` . * ,
`f`.`title` AS `feedTitle` ,
`f`.`url` AS `feedUrl` ,
`a`.`id` AS `authorId` ,
`a`.`name` AS `authorName` ,
`a`.`about` AS `authorAbout`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categories` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categories` AS `c` ON ec.category_id = c.id
INNER JOIN `authors` AS `a` ON e.author_id = a.id
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
) t GROUP BY author_id ORDER BY date DESC
LIMIT 5
相关文章