数据
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