按类别排名前 10 名的 mysql [英] top 10 by category mysql

问题描述

我有一个表格，根据一个人在该类别中花费的金额，我必须在每个类别中获得前 10 名.我的样本数据是

I have a table where I have to get top 10 in each category based on the amount spent by a person in that category. My sample data is

person category amount
roger  perfume   20
jim    perfume   50
joe    tv        5
jim    tv        25
kathy  car       40
alicia perfume   100

我必须在一个查询中得到这个.我知道限制对金额不起作用，例如限制金额 10.请帮忙

I gotta get this in one query. I know limit does not work on amount like limit amount 10. Please help

推荐答案

使用变量:

SELECT person, category, amount
FROM (
  SELECT person, category, amount,
         @rn := IF(@cat = category, @rn+1,
                   IF(@cat := category, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @cat := '') AS vars
  ORDER BY category, amount DESC) AS t
WHERE t.rn <= 10

@rn 用于按 amount 降序枚举每个 category 切片中的行.

@rn is used in order to enumerate rows within each category slice by descending amount order.

因此，外部查询返回每个 category 中排名最高的 10 行.如果特定类别的记录少于 10 条，则返回所有这些记录.

Hence, the outer query returns the 10 highest ranking rows per category. If less than 10 records exist for a specific category, then all of these records are returned.

此处演示

这篇关于按类别排名前 10 名的 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！