MySQL 5.5

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', 30),
       ('drink', 40),
       ('drink', 110),
       ('drink', 60),
       ('drink', 77),
       ('drink', 30),
       ('cookie', 20),
       ('cookie', 60),
       ('cookie', 22);

Records: 10  Duplicates: 0  Warnings: 0


SELECT
  f.id,
  f.category,
  f.price,
  COUNT(af.price) + 1 AS rk
FROM
  foods f
  LEFT JOIN foods af ON (
    f.category = af.category
    AND f.price < af.price
  )
GROUP BY
  f.id
ORDER BY
  rk;


id
category
price
rk


4
drink
110
1

9
cookie
60
1

6
drink
77
2

10
cookie
22
2

5
drink
60
3

8
cookie
20
3

3
drink
40
4

7
drink
30
5

2
drink
30
5

1
drink
20
7


SELECT
  f.id,
  f.category,
  f.price,
  COUNT(DISTINCT af.price) + 1 AS drk
FROM
  foods f
  LEFT JOIN foods af ON (
    f.category = af.category
    AND f.price < af.price
  )
GROUP BY
  f.id
ORDER BY
  drk;


id
category
price
drk


9
cookie
60
1

4
drink
110
1

6
drink
77
2

10
cookie
22
2

8
cookie
20
3

5
drink
60
3

3
drink
40
4

7
drink
30
5

2
drink
30
5

1
drink
20
6


SELECT
  f.id,
  f.category,
  f.price,
  COUNT(DISTINCT af.price) + 1 AS rn
FROM
  foods f
  LEFT JOIN foods af ON (
    f.category = af.category
    AND (
      f.price < af.price
      OR (
        f.price = af.price
        AND f.id > af.id
      )
    )
  )
GROUP BY
  f.id
ORDER BY
  rn;


id
category
price
rn


9
cookie
60
1

4
drink
110
1

6
drink
77
2

10
cookie
22
2

8
cookie
20
3

5
drink
60
3

3
drink
40
4

2
drink
30
5

7
drink
30
6

1
drink
20
6


SELECT
  f.id,
  f.category,
  f.price,
  COUNT(af.price) + 1 AS rn
FROM
  foods f
  LEFT JOIN foods af ON (
    f.category = af.category
    AND (
      f.price < af.price
      OR (
        f.price = af.price
        AND f.id > af.id
      )
    )
  )
GROUP BY
  f.id
HAVING
  rn <= 3
ORDER BY
  rn;


id
category
price
rn


9
cookie
60
1

4
drink
110
1

6
drink
77
2

10
cookie
22
2

5
drink
60
3

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