mysql存储过程实例统计最大销售数量

1.需求

统计某天的销售的商品中，数量大于m的最大的销售数量。

传入参数：销售日期，销售数量
返回参数：最大销售数量

2.数据库表

销售表

CREATE TABLE t_sale ( id int NOT NULL COMMENT ‘主键’, good_id
int NULL COMMENT ‘商品id’, sale_num int NULL COMMENT ‘销售数量’,
sale_time date NULL COMMENT ‘销售时间’, create_time datetime NULL
COMMENT ‘创建时间’, PRIMARY KEY (id) );

数据

INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (1, 1, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (2, 2, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (3, 2, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (4, 3, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (5, 3, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (6, 3, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);
INSERT INTO meal.t_sale(id, good_id, sale_num, sale_time,
create_time) VALUES (7, 3, 1, ‘2023-05-20’, ‘2023-05-20 21:44:17’);

数据如下：

注：sale_time：销售时间，sale_num:销售数量

3.存储过程

3.1创建存储过程

DELIMITER $$
CREATE
    PROCEDURE p_statis_goods (IN saledate VARCHAR(20),IN salenum INT,OUT f_count INT)
	-- 存储过程体
	BEGIN
	
		select max(sumNum) maxNum INTO f_count  from (
				select sum(sale_num) sumNum,good_id  from t_sale where sale_time = saledate  group by good_id having sum(sale_num) > salenum
		 ) a ;
		 
	commit; 
	
END$$

3.2调用存储过程

call p_statis_goods ('2023-05-20',2,@count);
select @count;

3.3结果

4.其他

删除存储过程：drop PROCEDURE p_statis_goods;