mysql中over partition by的具体使用

目录

• 前言
• 数据准备
• 通过分组聚合GROUP_CONCAT实现
• 通过关联查询及COUNT函数实现

/*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 (null,'P002',1,'豪华房',23),
(null,'P001',1,'高级标间',45),
(null,'P003',1,'高级标间',33),
(null,'P004',1,'经典房',65),
(null,'P003',1,'小型套房',45),
(null,'P002',2,'高级标间',331),
(null,'P005',2,'小型套房',223),
(null,'P001',1,'豪华房',99),
(null,'P002',3,'高级标间',65),
(null,'P003',2,'经典房',45),
(null,'P004',3,'标准双床房',67),
(null,'P005',2,'小型套房',34),
(null,'P001',1,'高级标间',43),
(null,'P002',3,'豪华房',56),
(null,'P001',3,'高级标间',77),
(null,'P005',2,'经典房',67),
(null,'P003',2,'高级标间',98),
(null,'P002',3,'经典房',23),
(null,'P004',2,'经典房',76),
(null,'P002',1,'小型套房',123);

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

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

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