Sql Query - 限制查询结果

Question

我很确定我们不能对我想做的事情使用 LIMIT 子句 - 所以想看看是否有任何其他方法可以实现这一点。

我有一个表，用于记录哪个用户访问了哪个商店。每次用户访问商店时，都会向该表中插入一行。

部分字段是

• shopping_id(主键)
• 店铺编号
• 用户编号

现在我想要的是 - 对于一组给定的商店，找到访问商店次数最多的前 5 名用户。

我可以一次做这 1 家商店:

select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5

这将给我访问 store_id=60 次数最多的 5 个用户

我想要做的是提供一个包含 10 个 store_id 的列表，并为每个商店获取访问该商店最多次数的 5 个用户

select store_id,user_id,count(1) as visits 
from shopping 
where store_id in  (60,61,62,63,64,65,66)
group by user_id,store_id 
order by visits desc Limit 5

这将不起作用，因为最后的限制将只返回 5 行，而不是每个商店的 5 行。

有关如何实现此目标的任何想法。我总是可以编写一个循环并一次传递 1 个商店，但想知道是否有更好的方法

Answer 1

使用两个用户变量并计算相同的连续store_id，您可以替换<= 5有你想要的任何限制

SELECT a.*
FROM (
 SELECT store_id, user_id, count(1) as visits 
 FROM shopping
 WHERE store_id IN (60,61,62,63,64,65,66)
 GROUP BY store_id, user_id
 ORDER BY store_id, visits desc, user_id
) a,
(SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.store_id THEN
   CASE WHEN @prev:=a.store_id THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 5

按要求编辑一些解释:

第一个子查询 (a) 是对数据进行分组和排序的子查询，因此您将拥有如下数据:

store_id | user_id | visits
---------+---------+-------
 60           1       5
 60           2       3
 60           3       1
 61           2       4
 61           3       2

第二个子查询(b)初始化用户变量@prev与 -1 和 @count与 1

然后我们从子查询 (a) 中选择所有数据来验证 case 中的条件.

• 验证我们看到的之前的 store_id ( @prev ) 是否与当前的 store_id 不同。自第一@prev等于 -1 没有任何东西匹配当前的 store_id 所以条件 <>是真的我们进入那么是第二种情况谁只是服务于改变值(value)@prev使用当前的 store_id。这是技巧，所以我可以更改两个用户变量 @count和 @prev在相同的条件下。

• 如果之前的 store_id 等于 @prev只需增加 @count变量。

• 我们检查计数是否在我们想要的值内，所以 <= 5

因此对于我们的测试数据:

step | @prev | @count | store_id | user_id | visits
-----+-------+--------+----------+---------+-------
  0      -1      1    
  1      60      1        60          1        5 
  2      60      2        60          2        3
  3      60      3        60          3        1
  4      61      1        61          2        4
  5      61      2        61          3        2