在MySQL查询中使用'distinct'

I have the following query.

我有以下查询。

SELECT p.author_name, p.author_id,
DISTINCT p.topic_id, t.title
FROM `ibf_posts` p, `ibf_topics` t
WHERE p.topic_id = t.tid
ORDER BY pid DESC
LIMIT 8"

When I run it, I get the following MySQL Error:

当我运行它时,我得到以下MySQL错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT p.topic_id, t.title FROM `ibf_posts` p, `ibf_topics` t WHERE p' at line 1

if I remove the DISTINCT keyword, then the query works without a problem.

如果我删除DISTINCT关键字,那么查询工作没有问题。

What am I doing wrong?

我究竟做错了什么?

This scheme is from Invision Power Board post and topic table. I am trying to get the title of the last 8 topics which has the newest posts. In the list of top latest posts, I don't want the same topic to appear more than once. I want a unique list of titles.

该方案来自Invision Power Board帖子和主题表。我想获得最新帖子的最后8个主题的标题。在最新的帖子列表中,我不希望同一主题出现多次。我想要一个独特的标题列表。

table: ibf_posts -pid -author_name -author_id -topic_id

table:ibf_posts -pid -author_name -author_id -topic_id

table: ibf_topics -tid -title

table:ibf_topics -tid -title

tid is same as topic_id

tid与topic_id相同

2 个解决方案

#1

It's

SELECT DISTINCT ...

You can't specify DISTINCT only for a single column; it only works for keeping complete duplicate records out of the result set.

您不能仅为单个列指定DISTINCT;它只适用于保留结果集中的完整重复记录。

#2

Distinct needs to be before p.author_name. You can't choose only p.topic_id to be distinct in this case. If what you're wanting to do is what I think it is, you should look up the GROUP BY clause.

不一定需要在p.author_name之前。在这种情况下,您不能只选择p.topic_id来区分。如果你想要做的就是我认为的那样,你应该查看GROUP BY子句。

#1