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

I have the following data structure: Articles have m:n Users

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

There are three tables: articles, users and articles_users (linking table)

有三个表:文章,用户和articles_users(链接表)

Now I need this query: Give me all Users who have recently written an Article.

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

Unfortunately, this query returns duplicate results:

不幸的是,此查询返回重复的结果:

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;

Result:

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 ...

Does anyone have an idea why DISTINCT isn't working here?

有谁知道为什么DISTINCT不在这里工作?

2 个解决方案

#1

This should group by author instead of of by article.

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

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
;

#2

because DISTINCT applies to the whole row (not just the users.id itself). as you can see all the rows returned are not unique. Try something like this, the idea behind the subquery is it gets the recent published date for each article_id

因为DISTINCT适用于整行(而不仅仅是users.id本身)。如您所见,返回的所有行都不是唯一的。尝试这样的事情,子查询背后的想法是获取每个article_id的最近发布日期

SELECT  users.id, 
        users.username, 
        articles.published
FROM    users 
        INNER JOIN articles_users 
            ON users.id = articles_users.user_id
        INNER JOIN articles 
            ON articles.id = articles_users.article_id
        INNER JOIN 
        (
            SELECT id, MAX(published) maxDate
            FROM articles
            GROUP BY id
        ) c ON  articles.id = c.ID AND 
                articles.published = c.maxDATE
-- ORDER BY articles.published DESC
-- LIMIT 25