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