前言

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