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