php - 限制搜索结果中字段值的数量

Question

我经营房地产网站，允许不同的用户添加他们的属性(property)。当有人搜索特定条件时，我们使用 select 语句和指定条件来选择匹配的属性，进行分页位并显示结果。

我们使用评级算法对每个属性进行评级，并使用它来确定显示的属性的优先级。

简化版:

name            type      bedrooms           user  scoregreen house     sale      two bedrooms       alex  6Blue one        rent      three bedrooms     jack  6Blue one        sale      three bedrooms     jack  4gray one        sale      three bedrooms     jack  6green one       rent      three bedrooms     jack  6purple one      rent      three bedrooms     jack  6green one       rent      three bedrooms     jack  6green one       rent      three bedrooms     gary  6

Now the problem is that sometimes a few properties have the same score. In these cases I don't want properties from one user to dominate a search result page, I want to set a limit to display a maximum of three properties of any given user in a search result page.

In the example, I don't want the properties owned by jack to dominate the first page, and properties of other users go to second page. This would upset other users and create a bad experience for visitors.

If I wanted to show only one property for a given user, I'd use Group by, but I'm not sure what to limit to a larger number ( three for instance). Is there anything I could do in mysql to achieve this?

EDIT:

Sorry if it wasnt clear enough.

The use field displays the user who added the particular property. A sample query could be

SELECT * FROM properties WHERE type = 'sale' LIMIT 5 ORDER BY score

结果可能是五个属性，全部由 jack 添加。我想确保结果中只包含特定用户添加的属性。这样其他用户添加的属性将有机会显示。

Answer 1

用DISTINCT就可以解决你的问题。在一个表中，一个列可能包含很多重复的值；有时您只想列出不同的(不同的)值。DISTINCT 关键字可用于仅返回不同的(不同的)值。例子SELECT DISTINCT user FROM table_name;