对商家的每个分类下的排名第一的数据进行统计,计算所占比例。显示出 商家名、商品总数、分类最多的总数和占比例。 仔细看完你就都懂了
- 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
- ;
效果