MYSQL 比较同一表中的连续行

数据

DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `counted_date` date NOT NULL COMMENT '日期',
  `item_no` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品代码',
  `qty` int(11) NOT NULL COMMENT '库存数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of inventory
-- ----------------------------
BEGIN;
INSERT INTO `inventory` VALUES (1, '2021-10-01', 'A', 20);
INSERT INTO `inventory` VALUES (2, '2021-10-01', 'A', 30);
INSERT INTO `inventory` VALUES (3, '2021-10-01', 'A', 45);
INSERT INTO `inventory` VALUES (4, '2021-10-01', 'A', 80);
INSERT INTO `inventory` VALUES (5, '2021-10-01', 'A', 100);
INSERT INTO `inventory` VALUES (6, '2021-10-02', 'B', 20);
INSERT INTO `inventory` VALUES (7, '2021-10-02', 'B', 30);
INSERT INTO `inventory` VALUES (8, '2021-10-02', 'B', 45);
INSERT INTO `inventory` VALUES (9, '2021-10-02', 'B', 80);
INSERT INTO `inventory` VALUES (10, '2021-10-02', 'B', 100);
INSERT INTO `inventory` VALUES (11, '2021-10-01', 'A', 20);
INSERT INTO `inventory` VALUES (12, '2021-10-01', 'A', 30);
INSERT INTO `inventory` VALUES (13, '2021-10-01', 'A', 45);
INSERT INTO `inventory` VALUES (14, '2021-10-01', 'A', 80);
INSERT INTO `inventory` VALUES (15, '2021-10-01', 'A', 100);
INSERT INTO `inventory` VALUES (16, '2021-10-02', 'B', 20);
INSERT INTO `inventory` VALUES (17, '2021-10-02', 'B', 30);
INSERT INTO `inventory` VALUES (18, '2021-10-02', 'B', 45);
INSERT INTO `inventory` VALUES (19, '2021-10-02', 'B', 80);
INSERT INTO `inventory` VALUES (20, '2021-10-02', 'B', 100);
INSERT INTO `inventory` VALUES (21, '2021-10-03', 'A', 20);
INSERT INTO `inventory` VALUES (22, '2021-10-03', 'A', 30);
INSERT INTO `inventory` VALUES (23, '2021-10-03', 'A', 45);
INSERT INTO `inventory` VALUES (24, '2021-10-03', 'A', 80);
INSERT INTO `inventory` VALUES (25, '2021-10-03', 'A', 100);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

要求：
计算A商品的相邻日期内新增的库存

第一步筛选

SELECT
	*
FROM 
	inventory g1
WHERE item_no = 'A'

排序

SELECT
	g1.*,
	@sep:= @sep + 1 as rn
FROM 
	inventory g1,
	(SELECT @sep := 0 ) t
WHERE item_no = 'A'

自连

WITH A as (
	SELECT
		g1.*,
		@sep:= @sep + 1 as rn
	FROM 
		inventory g1,
		(SELECT @sep := 0 ) t
	WHERE item_no = 'A'
)

SELECT
	a.item_no,
	a.counted_date startDate,
	b.counted_date endDate,
	b.qty - a.qty add_qty
FROM
	A a
	INNER JOIN A b on b.rn = a.rn + 1