前言
开发中遇到了这样一个需求：统计商品库存，产品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分组，得到结果。通过这种方式，也可以实现上面的需求。
