[转]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 ) stocksFROM (SELECT *FROM product_stock) tGROUP BY product_id,branch

查询结果：

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

SELECTproduct_id,branch,SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stockFROM (SELECT *FROM product_stock) tGROUP 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) sWHERE s.rank = 1

查询结果：

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