目标

1. MySQL TopN
2. Hive TopN

MySQL TopN

1. 创建表

   create table lesson(
   sno varchar(10) comment '学生编号',
   course varchar(10) comment '课程名称',
   score int(10) comment '分数'
   )

2. 创建表

   create table ad_list(
   ad_id string,
   url string,
   catalogs string
   ) 
   row format delimited
   fields terminated by '\t'

3. 查询 这个班级的 每个学科的成绩 最高的同学

   select
   l1.sno,l1.course,l1.score
   from
   lesson l1
   where (
   select count(*) from lesson l2
   where l1.course = l2.course and l1.score < l2.score
   ) = 0
   order by course,score desc;

Hive TopN

1. row number(over partition by course order by score desc ) as cnt,

2. collect_set(catalog) set集合(去重)