DISTINCT数据库中的列 [英] DISTINCT a column in a database

问题描述

我正在尝试对这样的查询执行DISTINCT子句

I'm trying to perform a DISTINCT clause on a query like this

SELECT DISTINCT username, wiki.text, wiki.date
FROM wiki_house
INNER JOIN wiki ON wiki_house.wiki_id = wiki.id
INNER JOIN users ON wiki.user_id = users.id
AND wiki_house.house_id = 1
AND wiki.language = 'it'
ORDER BY wiki.date DESC
LIMIT 10

这将返回:

username     wiki.text     wiki.date
mike         Hello         2010-03-09
mike         Helo          2010-03-08
kim          Whats...      2010-03-07
mike         When in...    2010-03-06
eddy         Hel           2010-03-05

我认为此查询应通过DISTINCT子句从不同的用户名返回最后10个Wiki，但它只是返回我最近的10个结果，因此，如果用户编写了2个版本的Wiki，则这些结果将显示在页面.

I thought this query should returned the last 10 wikis from different usernames, by the DISTINCT clause, but it simply return me the last 10 results, so if an user has wrote 2 versions of his wiki, these are showed in the page.

如何才能从不同的用户名中仅选择最后10个Wiki，像这样?

How can I select only the last 10 wikis from different usernames so something like this?

username     wiki.text     wiki.date
mike         Hello         2010-03-09
kim          Whats...      2010-03-07  
eddy         Hel           2010-03-05

推荐答案

使用子查询应该可以做到这一点.内部查询按日期对所有行进行排序，因此结果中每个用户名的第一个实例将是您要查找的行.

This should be possible using a subquery. The inner query orders all the rows by date, so the first instance of each username in that result would be the row you're looking for.

外部查询按用户名分组，如果不使用GROUP_CONCAT，则按AFAIK分组，这将始终采用包含用户名的每一行的第一个实例.

The outer query groups by username, and AFAIK if you're not using GROUP_CONCAT this will always take the first instance of each row containing the username.

SELECT username, wikitext, wikidate FROM
  (SELECT username, wiki.text AS wikitext, wiki.date AS wikidate
  FROM wiki_house
  INNER JOIN wiki ON wiki_house.wiki_id = wiki.id
  INNER JOIN users ON wiki.user_id = users.id
  AND wiki_house.house_id = 1
  AND wiki.language = 'it'
  ORDER BY wiki.date DESC)
GROUP BY username
LIMIT 10

如果这不起作用，请查看此类似问题的可接受答案还有另一种解决方案.您应该能够对其进行修改以适合您的需求.

If that's not working, have a look at the accepted answer for this similar question which has another solution. You should be able to modify it to suit your needs.

这篇关于DISTINCT数据库中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！