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

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

通过分组聚合GROUP_CONCAT实现

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

查询结果：

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

SELECTproduct_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

查询结果：

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

SELECT *
FROM (SELECTt.product_id,t.branch,t.stock,COUNT(*)     AS rankFROM product_stock tLEFT JOIN product_stock rON t.product_id = r.product_idAND t.branch = r.branchAND t.stock <= r.stockGROUP BY t.id) s
WHERE s.rank = 1

查询结果：

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