mysql 窗口函数 获取分类统计 每个分类下排名前三名

对商家的每个分类下的排名第一的数据进行统计，计算所占比例。显示出 商家名、商品总数、分类最多的总数和占比例。 仔细看完你就都懂了

select companyId, companyName, class4, totalNum, num, proportion
from (
         select
                md5(companyName) as companyId,
                companyName,
                class4,
                totalNum,
                num,
               FORMAT(num/totalNum,2)as proportion
                ,
                rank() over (
                    partition by companyName order by num desc
                    ) as rankNum
         from (
                  select t1.companyName, t2.totalNum, t1.num, t1.class4
                  from (
                           select count(id) as num, class4, companyName
                           from at_company_goods_03
                           group by companyName, class4
                       ) as t1
                           left join (select count(id) as totalNum, companyName
                                      from at_company_goods_03
                                      group by companyName) as t2 on t1.companyName = t2.companyName
              ) as z
     ) as zz
where rankNum = 1 ;

达到的效果 

 然后 在统计出 主分类下，挑选任意三个代表商品

select max(companyId),  companyName, max(totalNum), max(proportion), max(class1), max(class2), max(class3), max(class4),
       group_concat(goodsName1 order by companyName SEPARATOR '') newGoodsName1,
              group_concat(goodsImg1 order by companyName SEPARATOR '') newgoodsImg1,
              group_concat(goodsName2 order by companyName SEPARATOR '') newGoodsName2,
              group_concat(goodsImg2 order by companyName SEPARATOR '') newgoodsImg2,
              group_concat(goodsName3 order by companyName SEPARATOR '') newGoodsName3,
              group_concat(goodsImg3 order by companyName SEPARATOR '') newgoodsImg3
       from (
                  select companyId,
                         id,
                         idRank,
                         companyName,
                         totalNum,
                         proportion,
                         class1,
                         class2,
                         class3,
                         class4,
                         goodsName1,
                         goodsImg1,
                         goodsName2,
                         goodsImg2,
                         goodsName3,
                         goodsImg3
                  from (
                           select companyId,
                                  id,
                                  companyName,
                                  totalNum,
                                  proportion,
                                  class1,
                                  class2,
                                  class3,
                                  class4,
                                  goodsName as goodsName1,
                                  goodsImg  as goodsImg1,
                                  ''        as goodsName2,
                                  ''        as goodsImg2,
                                  ''        as goodsName3,
                                  ''        as goodsImg3,
                                  rank() over (
                                      partition by companyName order by id
                                      )     as idRank
                           from (
                                    select *
                                    from (
                                             select t1.companyId,
                                                    t2.id,
                                                    t1.companyName,
                                                    totalNum,
                                                    proportion,
                                                    t2.class1,
                                                    t2.class2,
                                                    t2.class3,
                                                    t2.class4,
                                                    goodsName,
                                                    goodsImg
                                             from at_company_goods_total_class as t1
                                                      left join at_company_goods_03 as t2
                                                                on t1.companyName = t2.companyName and t1.class4 = t2.class4
                                         ) as z
                                ) as zz
                       ) as zzz
                  where idRank = 1
                  union
                  select companyId,
                         id,
                         idRank,
                         companyName,
                         totalNum,
                         proportion,
                         class1,
                         class2,
                         class3,
                         class4,
                         goodsName1,
                         goodsImg1,
                         goodsName2,
                         goodsImg2,
                         goodsName3,
                         goodsImg3
                  from (
                           select companyId,
                                  id,
                                  companyName,
                                  totalNum,
                                  proportion,
                                  class1,
                                  class2,
                                  class3,
                                  class4,
                                  ''        as goodsName1,
                                  ''        as goodsImg1,
                                  goodsName as goodsName2,
                                  goodsImg  as goodsImg2,
                                  ''        as goodsName3,
                                  ''        as goodsImg3,
                                  rank() over (
                                      partition by companyName order by id
                                      )     as idRank
                           from (
                                    select *
                                    from (
                                             select t1.companyId,
                                                    t2.id,
                                                    t1.companyName,
                                                    totalNum,
                                                    proportion,
                                                    t2.class1,
                                                    t2.class2,
                                                    t2.class3,
                                                    t2.class4,
                                                    goodsName,
                                                    goodsImg
                                             from at_company_goods_total_class as t1
                                                      left join at_company_goods_03 as t2
                                                                on t1.companyName = t2.companyName and t1.class4 = t2.class4
                                         ) as z
                                ) as zz
                       ) as zzz
                  where idRank = 2
                  union
                  select companyId,
                         id,
                         idRank,
                         companyName,
                         totalNum,
                         proportion,
                         class1,
                         class2,
                         class3,
                         class4,
                         goodsName1,
                         goodsImg1,
                         goodsName2,
                         goodsImg2,
                         goodsName3,
                         goodsImg3
                  from (
                           select companyId,
                                  id,
                                  companyName,
                                  totalNum,
                                  proportion,
                                  class1,
                                  class2,
                                  class3,
                                  class4,

                                  ''        as goodsName1,
                                  ''        as goodsImg1,
                                  ''        as goodsName2,
                                  ''        as goodsImg2,
                                  goodsName as goodsName3,
                                  goodsImg  as goodsImg3,
                                  rank() over (
                                      partition by companyName order by id
                                      )     as idRank
                           from (
                                    select *
                                    from (
                                             select t1.companyId,
                                                    t2.id,
                                                    t1.companyName,
                                                    totalNum,
                                                    proportion,
                                                    t2.class1,
                                                    t2.class2,
                                                    t2.class3,
                                                    t2.class4,
                                                    goodsName,
                                                    goodsImg
                                             from at_company_goods_total_class as t1
                                                      left join at_company_goods_03 as t2
                                                                on t1.companyName = t2.companyName and t1.class4 = t2.class4
                                         ) as z
                                ) as zz
                       ) as zzz
                  where idRank = 3
              )as za group by companyName
;

效果