『MySQL』MySQL分组后添加行号

前言

永远相信美好的事情即将发生

---

背景

前段时间做报表的时候碰到这样一个问题，需要查询所有店铺在选定时间段内的每种商品的销售情况以及每家店铺在选定时间段的同比与环比，情况如下图所示：

当前时间数据：

店铺名	商品名	当前商品销售金额
store_a	a	100
store_a	b	50
store_b	a	75
store_c	a	30

同比与环比数据：

店铺名	总销售额
store_a	2000
store_b	1000
store_c	1500

其中当前时间查询的最小维度是每种商品的销售额，而同比和环比的最小维度则是每家店铺在时间段内的销售总额，现在需要将今年的数据和同比以及环比的数据进行关联，不要问我为什么要这样关联，我也很无奈啊

历程

1.踩坑

刚开始写SQL的时候没有注意到今年的数据维度是商品，直接用店铺进行关联，结果你懂的，关联多了…

2.思考

其实这个问题可以转换为 怎样用左表中每家店铺的任意一条数据去关联右表 ，或者可以进一步转化为 怎样用左表中每家店铺的第一条数据去关联右表 ，这样难度就降低了很多，可以理解为在一个条件关联（店铺名）中添加了一条无条件关联（第一条数据），既然提到了无条件关联，那首选的的就是 行号关联，有兴趣的可以看一下我以前的一篇博客 『MySQL』行号关联查询

3.爬坑

首先我们需要新建一张表，添加点测试数据

CREATE TABLE `test_table` (
  `shop_name` varchar(255) DEFAULT NULL,
  `goods_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_table` VALUES ('店铺甲', 'A');
INSERT INTO `test_table` VALUES ('店铺甲', 'B');
INSERT INTO `test_table` VALUES ('店铺甲', 'C');
INSERT INTO `test_table` VALUES ('店铺甲', 'D');
INSERT INTO `test_table` VALUES ('店铺甲', 'E');
INSERT INTO `test_table` VALUES ('店铺乙', 'A');
INSERT INTO `test_table` VALUES ('店铺乙', 'B');
INSERT INTO `test_table` VALUES ('店铺乙', 'C');
INSERT INTO `test_table` VALUES ('店铺丙', 'A');
INSERT INTO `test_table` VALUES ('店铺丙', 'B');
INSERT INTO `test_table` VALUES ('店铺丁', 'A');

查询结果如图：

接下来我们添加两个变量，一个存储行号，另一个存储店铺名。行号大家很好理解，那么这个店铺名变量的意义又是什么呢？很简单，这个变量就是用来实现分组的关键，比如第一条记录我们将 “店铺甲” 赋给了这个变量，在查询的时候，如果第二条记录的 shop_name 字段依旧是 “店铺甲” ,
那么我们就将这两条记录视为同一分组，若不是，则视为不同分组，然后再将第二条记录的 “shop_name” 字段赋给改变量，以此类推。

SELECT 
	@row_num := @row_num+1 AS row_num
   ,@shop_name := t.shop_name
   ,t.*
FROM
	test_table t,
	( SELECT @row_num := 0, @shop_name = '' ) n
ORDER BY
	t.shop_name DESC

查询结果如下：

离成功已经很接近了，接下来我们再按照刚才的逻辑对行号进行处理，大致逻辑就是如果这一条记录的 shop_name 字段与 @shop_name 变量数值相等，则行号自增，若不相等，则将行号手动设置为1

SELECT 
	IF(@shop_name = t.shop_name,@row_num:= @row_num+1,@row_num:= 1) AS row_num
   ,@shop_name := t.shop_name
   ,t.*
FROM
	test_table t,
	( SELECT @row_num := 0, @shop_name = '' ) n
ORDER BY
	t.shop_name DESC

查询结果如下：

注意点

• SQL中 “:=” 和 “=” 具有不同的含义，前者代表赋值，后者仅代表比较，在使用中千万要注意