SQL分组排序Limit

select type, max(price),variety from fruits group by type;

SELECT * FROM fruits GROUP BY TYPE ORDER BY price DESC LIMIT 1;

create procedure sp_demo()
begin
	declare _done int default 0;
	declare _type varchar(20);
	declare rs cursor for select distinct type from fruits;
	declare continue handler for sqlstate '02000' set _done = 1;
	
    drop table if exists temp_table;
	create table temp_table (
		type varchar(20),
		variety varchar(20),
		price decimal(8,2)
	) engine=innodb default charset=utf8;
	
    open rs;
    fetch next from rs into _type;
    repeat
    	insert into temp_table (type, variety, price) select type, variety, price from fruits where type = _type order by price desc limit 1;
    	fetch next from rs into _type;
    until _done end repeat;
    close rs;
    select * from temp_table;
    drop table if exists temp_table;
end

select f.type, f.variety, f.price
from (
   select type, max(price) as maxprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.maxprice;

select type, variety, price
from fruits
where price = (select max(price) from fruits as f where f.type = fruits.type);

select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price >= fruits.price
) <= 2;

FUNCTION_NAME(<argument>,<argument>...)
OVER
(<Partition-Clause><Order-by-Clause><Windowing Clause>)