2025年2月15日 星期六 甲辰(龙)年 腊月十五 设为首页 加入收藏
rss
您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

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

时间:03-06来源:作者:点击数:36

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

  • 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
  • ;

效果

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门