add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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