[转]MySQL实现over partition by(分组后对组内数据排序)

技术_编程语言 专栏收录该内容
128 篇文章 0 订阅

 

前言

 

开发中遇到了这样一个需求:统计商品库存,产品ID + 子产品名称都相同时,可以确定是同一款商品。当商品来自不同的渠道时,我们要统计每个渠道中最大的那一个。如果在Oracle中可以通过分析函数 OVER(PARTITION BY… ORDER BY…)来实现。在MySQL中应该怎么来实现呢。现在通过两种简单的方式来实现这一需求。

 

数据准备

 

/*Table structure for table `product_stock` */
CREATE TABLE `product_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_id` varchar(10) DEFAULT NULL COMMENT '产品ID',
  `channel_type` int(11) DEFAULT NULL COMMENT '渠道类型',
  `branch` varchar(10) DEFAULT NULL COMMENT '子产品',
  `stock` int(11) DEFAULT NULL COMMENT '库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;

/*Data for the table `product_stock` */

insert  into `product_stock`
(`id`,`product_id`,`channel_type`,`branch`,`stock`) 
values (1,'P002',1,'豪华房',23),
(2,'P001',1,'高级标间',45),
(3,'P003',1,'高级标间',33),
(4,'P004',1,'经典房',65),
(5,'P003',1,'小型套房',45),
(6,'P002',2,'高级标间',331),
(7,'P005',2,'小型套房',223),
(8,'P001',1,'豪华房',99),
(9,'P002',3,'高级标间',65),
(10,'P003',2,'经典房',45),
(11,'P004',3,'标准双床房',67),
(12,'P005',2,'小型套房',34),
(13,'P001',1,'高级标间',43),
(14,'P002',3,'豪华房',56),
(15,'P001',3,'高级标间',77),
(16,'P005',2,'经典房',67),
(17,'P003',2,'高级标间',98),
(18,'P002',3,'经典房',23),
(19,'P004',2,'经典房',76),
(20,'P002',1,'小型套房',123);

 

通过分组聚合GROUP_CONCAT实现

 

SELECT
  product_id,
  branch,
  GROUP_CONCAT(t.stock ORDER BY t.stock DESC ) stocks
FROM (SELECT *
      FROM product_stock) t
GROUP BY product_id,branch

 

查询结果:

 

product_idbranchstocks
P001豪华房99
P001高级标间77,45,43
P002小型套房123
P002经典房23
P002豪华房56,23
P002高级标间331,65
P003小型套房45
P003经典房45
P003高级标间98,33
P004标准双床房67
P004经典房76,65
P005小型套房223,34
P005经典房67

 

这也许并不是我们想要的结果,我们只要stocks中的最大值就可以,那么我们只要用SUBSTRING_INDEX函数截取一下就可以:

 

SELECT
  product_id,
  branch,
  SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stock
FROM (SELECT *
      FROM product_stock) t
GROUP BY product_id,branch

 

查询结果:

 

product_idbranchstock
P001豪华房99
P001高级标间77
P002小型套房123
P002经典房23
P002豪华房56
P002高级标间331
P003小型套房45
P003经典房45
P003高级标间98
P004标准双床房67
P004经典房76
P005小型套房223
P005经典房67

 

通过关联查询及COUNT函数实现

 

SELECT *
FROM (SELECT
        t.product_id,
        t.branch,
        t.stock,
        COUNT(*)     AS rank
      FROM product_stock t
        LEFT JOIN product_stock r
          ON t.product_id = r.product_id
            AND t.branch = r.branch
            AND t.stock <= r.stock
      GROUP BY t.id) s
WHERE s.rank = 1

 

查询结果:

 

product_idbranchstockrank
P003小型套房451
P002高级标间3311
P005小型套房2231
P001豪华房991
P003经典房451
P004标准双床房671
P002豪华房561
P001高级标间771
P005经典房671
P003高级标间981
P002经典房231
P004经典房761
P002小型套房1231

 

通过关联表本身,联接条件中:t.stock <= r.stock,当t.stock = r.stock时,COUNT出来的数量是1,当t.stock < r.stock时,COUNT出来的数量2,3,4…由此可以给所有的数据根据stock字段做一个排序,而这个排序中所有为1的,就是我们所需求的数据,然后通过按id分组,得到结果。通过这种方式,也可以实现上面的需求。

 

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值