mysql分组后组内排序，组外在排序

• 建表写数据语句

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;



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

这也许并不是我们想要的结果，我们只要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

• 通过关联查询及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

• 分组后组内排序输出排序后的

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
ORDER BY
    b.branch DESC

partition by branch order by stock desc  按branch 分组，组内按stock 降序