具有两个 INNER JOIN 的 MySQL 查询在结果中返回重复的条目

Question

我有以下数据结构:文章有 m:n 个用户

共有三个表:articles、users 和articles_users(链接表)

现在我需要这个查询:给我最近写了一篇文章的所有用户。

不幸的是，这个查询返回重复的结果:

SELECT  DISTINCT users.id, 
        users.username, 
        articles.published, 
        articles_users.user_id
FROM    users 
        INNER JOIN articles_users 
            ON users.id = articles_users.user_id
        INNER JOIN articles     
            ON articles.id = articles_users.article_id
ORDER BY articles.published DESC
LIMIT 25;

结果:

id      username        published   user_id
113     silva_mihat     2012-10-30  112
228     paula_tille     2012-10-27  258
228     paula_tille     2012-10-26  258
631     andrea_gurkow   2012-10-24  631
275     hubert_mayer    2012-10-24  275
198     annette_mulger  2012-10-22  198
255     uta_zuffter     2012-10-22  235
and so on ...

有谁知道为什么 DISTINCT 在这里不起作用？

Answer 1

这应该按作者而不是按文章分组。

select
  users.id,
  users.username,
  maxPublished
from users
inner join (
  select
    max(articles.published) as maxPublished,
    articles_users.user_id as userID
  from articles
  join articles_users on articles_users.article_id = articles.id
  group by articles_users.user_id
) as p on users.id = userID
order by maxPublished desc
limit 25
;