MySQL实现over partition by（分组后对组内数据排序）

前言

开发中遇到了这样一个需求：统计商品库存，产品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);

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34

通过分组聚合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

    1
    2
    3
    4
    5
    6
    7

查询结果：
product_id    branch    stocks
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

    1
    2
    3
    4
    5
    6
    7

查询结果：
product_id    branch    stock
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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

查询结果：
product_id    branch    stock    rank
P003    小型套房    45    1
P002    高级标间    331    1
P005    小型套房    223    1
P001    豪华房    99    1
P003    经典房    45    1
P004    标准双床房    67    1
P002    豪华房    56    1
P001    高级标间    77    1
P005    经典房    67    1
P003    高级标间    98    1
P002    经典房    23    1
P004    经典房    76    1
P002    小型套房    123    1

通过关联表本身，联接条件中：t.stock <= r.stock，当t.stock = r.stock时，COUNT出来的数量是1，当t.stock < r.stock时,COUNT出来的数量2,3,4…由此可以给所有的数据根据stock字段做一个排序，而这个排序中所有为1的，就是我们所需求的数据，然后通过按id分组，得到结果。通过这种方式，也可以实现上面的需求。
————————————————
版权声明：本文为CSDN博主「MrCao杰罗尔德」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_37797991/article/details/80511855