mysql - MySQL - 无法在总和为零的情况下获得每日总和,已经使用日历表
问题描述
我觉得我大约有 90% 的方法来解决这个问题,它可能只需要某种子查询,但我不知道它会是什么样子。
我按照这个网站上的指南,并通过这个查询得到了预期的结果:
SELECT calendar_dates.cdate, IFNULL(SUM(daily_inv_history.quantity),0) AS daily_inv
FROM daily_inv_history
RIGHT JOIN calendar_dates
ON (DATE(daily_inv_history.snapshot_date) = calendar_dates.cdate)
WHERE (
calendar_dates.cdate BETWEEN (
SELECT MIN(DATE(snapshot_date)) FROM daily_inv_history)
AND (SELECT MAX(DATE(snapshot_date)) FROM daily_inv_history))
GROUP BY calendar_dates.cdate
我得到了这个正确的结果,其中总和为 0 的天数仍然返回:
cdate daily_inv
2019-08-15 4
2019-08-16 1
2019-08-17 0
2019-08-18 0
2019-08-19 22
但是当我尝试通过将 WHERE 更改为此添加另一个 WHERE 条件时:
WHERE (
daily_inv_history.sku='skuA', AND
calendar_dates.cdate BETWEEN (
SELECT MIN(DATE(snapshot_date)) FROM daily_inv_history)
AND (SELECT MAX(DATE(snapshot_date)) FROM daily_inv_history))
我得到这个结果,总和为 0 的日子不再存在。SQLFiddle 可以在这里找到。
cdate daily_inv
2019-08-15 4
2019-08-16 1
2019-08-19 12
如何使 daily_inv 为 0 的日期仍然返回并添加条件?
编辑:我尝试了daily_inv_history.sku='skuA'
加入 JOIN ... ON 的建议,但这导致真实数据库的性能极差。还有其他想法吗?
这是架构和示例表创建代码:
CREATE TABLE `daily_inv_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`snapshot_date` date NOT NULL,
`sku` varchar(45) NOT NULL,
`quantity` int(11) NOT NULL,
`fulfillment_center_id` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uc_daily_inv_hist` (`snapshot_date`,`sku`,`fulfillment_center_id`)
) ENGINE=InnoDB AUTO_INCREMENT=924974 DEFAULT CHARSET=utf8;
INSERT INTO daily_inv_history (snapshot_date, sku, quantity, fulfillment_center_id)
VALUES
('2019-08-15 07:00:00', 'skuA', '2', 'fc1'),
('2019-08-15 07:00:00', 'skuA', '2', 'fc2'),
('2019-08-16 07:00:00', 'skuA', '1', 'fc1'),
('2019-08-19 07:00:00', 'skuA', '5', 'fc1'),
('2019-08-19 07:00:00', 'skuA', '7', 'fc2'),
('2019-08-19 07:00:00', 'skuB', '10', 'fc2');
CREATE TABLE `calendar_dates` (
`cdate` date DEFAULT NULL,
KEY `calendar_dates_idx_cdate` (`cdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO calendar_dates (cdate)
VALUES
('2019-08-13'),
('2019-08-14'),
('2019-08-15'),
('2019-08-16'),
('2019-08-17'),
('2019-08-18'),
('2019-08-19'),
('2019-08-20');
解决方案
推荐阅读
- ios - 具有多个组件的 UIPickerView 使应用程序崩溃
- flutter - 允许在录制视频期间播放音乐,例如 snapchat flutter
- c# - C# code being optimized in Debug mode with Optimize code unchecked
- javascript - React Flow showing Cannot resolve module error for create-react-app
- python - 数据框索引操作无法按预期工作
- azure - 具有 B2C 身份验证的 Azure 函数
- c# - 如何在 C# 中捕获异常?
- flutter - 在一页中使用多颤动块
- node.js - 将 React 迁移到 SSR 的问题。从“@babel/runtime/helpers/esm/extends”导入 _extends
- android - intent.resolveActivity() 为 ACTION_SEND 意图返回 null