对比mysql和hive：mysqlzhong ,分组求不同组的前几条记录

create table mian61(
    hobby varchar(20),
    name varchar(20),
    age int
)charset=utf8;

insert into mian61 values
('a','huangxiaoming',45),
('c','huangxiaoming',45),
('d','huangxiaoming',45),
('f','huangxiaoming',45),
('b','huangzitao',36),
('c','huangzitao',36),
('d','huangzitao',36),
('e','huangzitao',36),
('c','huanglei',41),
('d','huanglei',41),
('e','huanglei',41),
('a','liushishi',22),
('d','liushishi',22),
('e','liushishi',22),
('e','liudehua',39),
('f','liudehua',39),
('d','liudehua',35),
('d','liuyifei',35),
('a','huangxiaoming',45),
('e','liuyifei',35);

select 
	a.* 
from 
	mian61 a 
where
	(select count(*) from mian61 b where b.hobby= a.hobby and a.age > b.age)<2 
order by 
	a.hobby,a.age desc;

create table mian62(
    sid int,
    name varchar(20),
    subject varchar(20),
    score int
)charset=utf8;

insert into mian62 values
(1,'张三','语文',90),
(1,'张三','数学',80),
(1,'张三','外语',70),
(1,'张三','物理',55),
(1,'张三','化学',59),
(2,'李四','语文',88),
(2,'李四','数学',78),
(2,'李四','外语',71),
(2,'李四','物理',51),
(2,'李四','化学',60),
(3,'王五','语文',95),
(3,'王五','数学',83),
(3,'王五','外语',80),
(3,'王五','物理',60),
(3,'王五','化学',58);

select 
	*
from 
	mian62 a
where 
	(select count(*) from mian62 b where a.name=b.name and a.score < b.score)<2
order by 
	a.name,a.score desc;

select 
	*
from 
	mian62 a
where 
	(select count(*) from mian62 b where a.subject=b.subject and a.score < b.score)<2
order by 
	a.subject,a.score desc;