我很确定我们不能将LIMIT子句用于我想做的事情 – 所以我想找到是否有其他方法可以实现这一点.
我有一个表,用于捕获哪个用户访问了哪个商店.每次用户访问商店时,都会在此表中插入一行.
有些领域是
> shopping_id(主键)
> store_id
> user_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
我想要做的是提供10个store_ids的列表,并为每个商店提取已访问该商店最多倍的5个用户
select store_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个商店,但想知道是否有更好的方法
解决方法
使用两个用户变量并计算相同的连续store_id,您可以用您想要的任何限制替换< = 5
SELECT a.*
FROM (
SELECT store_id,count(1) as visits
FROM shopping
WHERE store_id IN (60,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)用-1和@count初始化用户变量@prev
然后我们从子查询中选择所有数据(a)验证案例中的条件.
>验证我们看到的先前store_id(@prev)与当前store_id不同.
由于第一个@prev等于-1,因此没有任何东西与当前store_id匹配,因此条件<>是的,我们输入的是第二种情况,它只是用当前的store_id更改值@prev.这是技巧,所以我可以在相同的条件下更改两个用户变量@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
