如何使用WHERE id IN(1,2,3,4)获得均匀分布 [英] How can I get an even distribution using WHERE id IN(1,2,3,4)
问题描述
我有一个查询,该查询从users
表中拉出喜欢特定对象的用户.等级存储在ratings
表中.到目前为止,我提出的查询看起来像这样:
I have a query that is pulling users who liked a specific object from a users
table. Ratings are stored in a ratings
table. The query I have come up with so far looks like this:
SELECT user.id, user.name, user.image
FROM users
LEFT JOIN ratings ON ratings.userid = user.id
WHERE rating.rating > 0
AND rating.objectId IN (1,2,3,4)
当每个ID仅需要3个左右的结果时,我希望能够在此查询上放置一个LIMIT
,以避免返回所有结果.例如,如果我只放置一个LIMIT 12
,我可能会得到8个记录,每个记录具有一个ID,其他ID分别具有1或2个记录,即ID之间的分布不均匀.
I want to be able to put a LIMIT
on this query, to avoid returning all the results, when I only need 3 or so results for each ID. If I just put a LIMIT 12
for example, I might get 8 records with one id, and 1 or 2 each for the others - i.e. an uneven distribution across the IDs.
有没有一种写此查询的方法来保证(假设一个对象被点赞"了至少3次),对于列表中的每个ID,我都会得到3个结果?
Is there a way to write this query so as to guarantee that (assuming an object has been "liked" at least three times), I get three results for each of the ids in the list?
推荐答案
通过设置行号和变量,然后过滤结果以显示仅第1-3行应该起作用
By setting the row number whit variables, and then filter that result to show only row 1-3 should work
SET @last_objectId = 0;
SET @count_objectId = 0;
SELECT id, name, image FROM (
SELECT
user.id,
user.name,
user.image,
@count_objectId := IF(@last_objectId = rating.objectId, @count_objectId, 0) + 1 AS rating_row_number,
@last_objectId := rating.objectId
FROM users
LEFT JOIN ratings ON (ratings.userid = user.id)
WHERE
rating.rating > 0 AND
rating.objectId IN (1,2,3,4)
ORDER BY rating.objectId
) AS subquery WHERE rating_row_number <= 3;
这篇关于如何使用WHERE id IN(1,2,3,4)获得均匀分布的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!