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

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

mysql> SELECT product_id, branch, GROUP_CONCAT(stock) FROM product_stock GROU
P BY product_id,branch;
±-----------±-----------±--------------------+
| product_id | branch | GROUP_CONCAT(stock) |
±-----------±-----------±--------------------+
| P001 | 豪华房 | 99 |
| P001 | 高级标间 | 45,43,77 |
| P002 | 小型套房 | 123 |
| P002 | 经典房 | 23 |
| P002 | 豪华房 | 23,56 |
| P002 | 高级标间 | 331,65 |
| P003 | 小型套房 | 45 |
| P003 | 经典房 | 45 |
| P003 | 高级标间 | 33,98 |
| P004 | 标准双床房 | 67 |
| P004 | 经典房 | 65,76 |
| P005 | 小型套房 | 223,34 |
| P005 | 经典房 | 67 |
±-----------±-----------±--------------------+
13 rows in set (0.00 sec)

mysql> SELECT product_id, branch, GROUP_CONCAT(stock order by stock desc) as
stocks FROM product_stock 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 |
±-----------±-----------±---------+
13 rows in set (0.00 sec)

mysql> select product_id,branch,GROUP_CONCAT(t.stock order by t.stock DESC) stoc
ks 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 |
±-----------±-----------±---------+
13 rows in set (0.07 sec)

mysql> SELECT product_id, branch, SUBSTRING_INDEX(GROUP_CONCAT(stock order by
stock desc),’,’,1) as stocks FROM product_stock GROUP BY product_id,branch;
±-----------±-----------±-------+
| product_id | branch | stocks |
±-----------±-----------±-------+
| 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 |
±-----------±-----------±-------+
13 rows in set (0.02 sec)

mysql> SELECT product_id,branch,SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.
stock DESC ),’,’,1) stocks FROM (SELECT *FROM product_stock) t GROUP BY product_
id,branch;
±-----------±-----------±-------+
| product_id | branch | stocks |
±-----------±-----------±-------+
| 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 |
±-----------±-----------±-------+
13 rows in set (0.02 sec)

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

mysql> 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;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘rank
FROM product_stock t
LEFT JOIN product_stock r
ON t’ at line 6

mysql> select
-> b.product_id,
-> b.branch,
-> b.stock
-> from(select
-> product_id,
-> branch,
-> stock,
-> row_number() over(partition by branch order by stock desc) as n
-> from
-> product_stock) b;
±-----------±-----------±------+
| product_id | branch | stock |
±-----------±-----------±------+
| P005 | 小型套房 | 223 |
| P002 | 小型套房 | 123 |
| P003 | 小型套房 | 45 |
| P005 | 小型套房 | 34 |
| P004 | 标准双床房 | 67 |
| P004 | 经典房 | 76 |
| P005 | 经典房 | 67 |
| P004 | 经典房 | 65 |
| P003 | 经典房 | 45 |
| P002 | 经典房 | 23 |
| P001 | 豪华房 | 99 |
| P002 | 豪华房 | 56 |
| P002 | 豪华房 | 23 |
| P002 | 高级标间 | 331 |
| P003 | 高级标间 | 98 |
| P001 | 高级标间 | 77 |
| P002 | 高级标间 | 65 |
| P001 | 高级标间 | 45 |
| P001 | 高级标间 | 43 |
| P003 | 高级标间 | 33 |
±-----------±-----------±------+
20 rows in set (0.00 sec)

mysql> SELECT product_id,branch,max(stock),count(*) AS q
-> FROM
-> product_stock
-> GROUP BY
-> branch,product_id;
±-----------±-----------±-----------±–+
| product_id | branch | max(stock) | q |
±-----------±-----------±-----------±–+
| P002 | 豪华房 | 56 | 2 |
| P001 | 高级标间 | 77 | 3 |
| P003 | 高级标间 | 98 | 2 |
| P004 | 经典房 | 76 | 2 |
| P003 | 小型套房 | 45 | 1 |
| P002 | 高级标间 | 331 | 2 |
| P005 | 小型套房 | 223 | 2 |
| P001 | 豪华房 | 99 | 1 |
| P003 | 经典房 | 45 | 1 |
| P004 | 标准双床房 | 67 | 1 |
| P005 | 经典房 | 67 | 1 |
| P002 | 经典房 | 23 | 1 |
| P002 | 小型套房 | 123 | 1 |
±-----------±-----------±-----------±–+
13 rows in set (0.05 sec)

参考：https://blog.csdn.net/m0_37797991/article/details/80511855