MySQL查询fruits表中不同s_id下价格最高的水果名称以及这些水果的价格

fruits表如下，利用MySQL查询不同s_id下价格最高的水果名称f_name及其价格f_price

img

注意：同一s_id下价格最高水果可能不止一种，需显示所有水果名称

fruits表代码：

#创建fruits表
create table fruits(
f_id char(10) not null,
s_id int not null,
f_name varchar(255) not null,
f_price decimal(8,2) not null,
primary key(f_id));

#插入数据
insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',25.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('b5',107,'xxxx',3.6);

既然你都给了sql，那就写一下吧
SELECT
*
FROM
fruits t1
WHERE
( SELECT COUNT( 1 ) FROM fruits t2 WHERE t1.s_id = t2.s_id AND t2.f_price > t1.f_price ) < 1
ORDER BY
s_id

img

img