mysql - 存储过程只给出有限数量的结果
问题描述
我的 StoredProcedure 只给我结果中的最大行数,等于表中的条目数。
所以call SP_Dashboard_getTransactionsPerDay("2018-03-19", "2018-04-25")
会给我:
date | total
-----------------
2018-03-19| 0
2018-03-19| 0
如果表中有 2 个条目,event
而不是请求的完整 38 天。如果 中有 6 个条目,则相同的调用给我 6 行,如果event
中至少有 38 个或更多条目,则给我 38 行event
。
即使表格中没有条目,是否有任何方法可以获得所有请求日期的结果event
?
我的 SP 如下所示:
CREATE PROCEDURE `SP_Dashboard_getTransactionsPerDay`(STARTDATE DATE,ENDDATE DATE)
BEGIN
set @i := -1;
SELECT
DATE(ADDDATE(STARTDATE, INTERVAL @i:=@i+1 DAY)) AS date,
IFNULL(
(
SELECT
COUNT(*) FROM event AS m2
WHERE
DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
AND `m2`.`status` = 'ok'
),
0) AS total
FROM
event AS m1;
HAVING
@i < DATEDIFF(ENDDATE, STARTDATE);
END
表 DDL:
CREATE TABLE `event` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`status` enum('new','ok','error') COLLATE utf8_bin NOT NULL DEFAULT 'new',
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
解决方案
通过将以下内容添加到我的数据库中,添加日历/日期表为我解决了这个问题:
一个新表:
-- -----------------------------------------------------
-- Table `dates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dates`;
CREATE TABLE `dates` (
`date` DATE NOT NULL,
PRIMARY KEY (`date`)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
自动填充新表的存储过程:
-- -----------------------------------------------------
-- StoredProcedure `SP_filldates`
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS SP_filldates;
DELIMITER $$
CREATE PROCEDURE SP_filldates(STARTDATE DATE, ENDDATE DATE)
BEGIN
WHILE STARTDATE <= ENDDATE DO
INSERT INTO dates (date) VALUES (STARTDATE);
SET STARTDATE = date_add(STARTDATE, INTERVAL 1 DAY);
END WHILE;
END;
$$
DELIMITER ;
CALL SP_filldates('2018-01-01', '2018-12-31');
以及我最初发布的存储过程的修改:
-- -----------------------------------------------------
-- StoredProcedure `SP_Dashboard_getTransactionsPerDay`
-- -----------------------------------------------------
DROP PROCEDURE
IF EXISTS SP_Dashboard_getTransactionsPerDay;
DELIMITER $$
CREATE PROCEDURE SP_Dashboard_getTransactionsPerDay(
STARTDATE DATE,
ENDDATE DATE
)
BEGIN
SET @i := -1;
SELECT
DATE(ADDDATE(STARTDATE, INTERVAL @i := @i + 1 DAY)) AS date,
IFNULL(
(
SELECT COUNT(*)
FROM event AS m2
WHERE
DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
AND `m2`.`status` = 'ok'
),
0
) AS total
FROM
dates AS m1
HAVING
@i < DATEDIFF(ENDDATE, STARTDATE);
END$$
DELIMITER ;
此修复适用于最多 365 行的所有请求,因为在这种情况下,日期表仅包含 365 个条目。如果需要更多结果,则日期表必须包含更多日期。
推荐阅读
- c++ - find element in array of strings
- grafana - pfsense grafana Dashboard
- javascript - 计算 collatz 猜想的代码没有输出
- c# - C# 为什么 Shell32.Shell.Windows() 这么慢?
- java - Java - log4 既不打印到控制台也不打印到文件
- mongodb - 使用自定义键使用 MongoDB $arrayToObject
- c# - Excel 文件大小在使用 EPPlus 更新后变得臃肿
- python - 如何有条件地将 pandas DataFrame 值更改为 f 字符串?
- python - 如何在 tkinter 中找到小部件相对于窗口的位置?
- sql - Google Cloud SQL:插入行时列不存在错误