前言
开发中遇到了这样一个需求：统计商品库存，产品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
查询结果：
| 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 ，当t.stock = r.stock时，COUNT出来的数量是1，当t.stock
到此这篇关于mysql中over partition by的具体使用的文章就介绍到这了,更多相关mysql over partition by内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
