MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE foods (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '没啥意义的主键',
  category VARCHAR(10) COMMENT '类别',
  price INT COMMENT '价格'
);

INSERT INTO foods (category, price)
VALUES ('drink', 20),
       ('drink', 60),
       ('drink', 40),
       ('drink', 110),
       ('drink', 60),
       ('drink', 77),
       ('cookie', 20),
       ('cookie', 60),
       ('cookie', 22);

Records: 9  Duplicates: 0  Warnings: 0


SELECT
  id,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS drk
FROM
  foods;


id
category
price
rn
rk
drk


8
cookie
60
1
1
1

9
cookie
22
2
2
2

7
cookie
20
3
3
3

4
drink
110
1
1
1

6
drink
77
2
2
2

2
drink
60
3
3
3

5
drink
60
4
3
3

3
drink
40
5
5
4

1
drink
20
6
6
5


SELECT
  w.id,
  w.category,
  w.price
FROM
  (
    SELECT
      id,
      category,
      price,
      ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM
      foods
  ) w
WHERE
  w.rn <= 3;


id
category
price


8
cookie
60

9
cookie
22

7
cookie
20

4
drink
110

6
drink
77

2
drink
60

id	category	price	rn	rk	drk
8	cookie	60	1	1	1
9	cookie	22	2	2	2
7	cookie	20	3	3	3
4	drink	110	1	1	1
6	drink	77	2	2	2
2	drink	60	3	3	3
5	drink	60	4	3	3
3	drink	40	5	5	4
1	drink	20	6	6	5