mysql分组求最大值、最小值、最大的前3位或者前n位

本文分享下我第一次面试碰到的面试题，当时傻不拉几的想着用limit，结果怎么也写不出来，哎，想想当时脑子就是笨，回家仔细一想并不难嘛

--一、 原始表名为spec_base，有5列，分别是：类别kindid,类型typeid,品牌号brand_id,规格号pack_bar
--二、需要给出哪个类别和类型下的哪个品牌规格数最多
--每组只求最大值
--1. 相关子查询（先用max求出最大值，然后找到值等于最大值对应的记录）
select  kindid, typeid, brand_id, all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec_base 
group by kindid, typeid, brand_id
) s1
where all1 = (
select max(all1) 
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 

group by kindid, typeid, brand_id
) s2
where s1.kindid = s2.kindid and s1.typeid = s2.typeid
)
--求最小值只要把max换成min即可

--2. 自连接（先用max求出最大值，然后用join内连接找到最大值对应的记录）
select  s1.kindid, s1.typeid, s1.brand_id, s1.all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s1
inner join  
(
select kindid, typeid, max(all1) as all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) a
group by kindid, typeid
) s2
on s1.kindid = s2.kindid and s1.typeid = s2.typeid and s1.all1 = s2.all1
--求最小值只要把max换成min即可

--3.用not exists（利用最大值不可能小于其他值这个点）
select  kindid, typeid, brand_id, all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s1
where not exists (
select 1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s2
where s1.kindid = s2.kindid and s1.typeid = s2.typeid  and s1.all1 < s2.all1
)
--求最小值只要把<换成>即可

--三、需要给出哪个类别和类型下的规格数最多前3个品牌
--每组只求最大的前3个值
--1. 用having（利用s1.all1 < s2.all1对应的笛卡儿积出现次数来筛选，假如总数是n，则最小值肯定是出现n-1次，同理，最大值出现0次）
select  s1.kindid, s1.typeid, s1.brand_id, s1.all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s1
left join 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s2
on s1.kindid = s2.kindid and s1.typeid = s2.typeid and s1.all1 < s2.all1   --如果求最小的只需把<改成>即可
group by s1.kindid,s1.typeid, s1.brand_id, s1.all1
having count(s2.typeid) < 3   --如果求最大值只需把3改成1即可
order by s1.kindid, s1.typeid, s1.brand_id, s1.all1 desc

--2. 用相关子查询（同上，也是利用s1.all1 < s2.all1对应的笛卡儿积出现次数来筛选）
select  s1.kindid, s1.typeid, s1.brand_id, s1.all1
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s1
where 3>(
select count(*)
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s2
where s1.kindid=s2.kindid and s1.typeid = s2.typeid and s1.all1 < s2.all1
)
order by s1.kindid,s1.typeid, s1.brand_id, s1.all1 desc

--3. 使用row_number() over(partition by ... order by ...)(impala和hive支持该语法)
select s1.kindid, s1.typeid, s1.all1
from (
select s1.kindid, s1.typeid, s1.all1, row_number() over (partition by s1.kindid, s1.typeid order by s1.all1 desc) as od
from 
(
select kindid, typeid, brand_id, count(distinct pack_bar) as all1
from spec 
group by kindid, typeid, brand_id
) s1
) ss1
where od <=3
order by od desc