如何更改此SQL,以便我可以从每个作者那里获得一个帖子?

2022-03-13 00:00:00 sql mysql inner-join
在下面的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 

相关文章