mysql实现分组前排序 mysql分组后组内排序

前言

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