mysql - 具有日期范围条件的 Group By 和 SUM 的 sql
问题描述
下面是Invoices
表格:
我正在尝试进行一个 sql 查询,它根据due_date
范围和balance_amount
group by的总和为我提供输出company_id
我的尝试:
select invoices.company_id,
SUM(invoices_cmonth.balance_amount) as cmonth,
SUM(invoices_1month.balance_amount) as 1month,
SUM(invoices_2month.balance_amount) as 2month
from `invoices`
LEFT JOIN invoices invoices_cmonth
ON (invoices.company_id = invoices_cmonth.company_id and invoices_cmonth.due_date >= '2021-11-10')
LEFT JOIN invoices invoices_1month
ON (invoices.company_id = invoices_1month.company_id and invoices_1month.due_date < '2021-11-10' and invoices_1month.due_date >= '2021-10-10')
LEFT JOIN invoices invoices_2month
ON (invoices.company_id = invoices_2month.company_id and invoices_2month.due_date < '2021-10-10' and invoices_2month.due_date >= '2021-9-10')
where invoices.`status` = 'ACTIVE'
and invoices.`balance_amount` > 0
and `invoices`.`deleted_at` is null
group by invoices.`company_id`
但它给了我错误的余额数字。
解决方案
invoices
我建议使用条件聚合对各种时间窗口进行一次遍历表:
SELECT
company_id,
SUM(CASE WHEN due_date >= '2021-11-10' THEN balance_amount ELSE 0 END) AS cmonth,
SUM(CASE WHEN due_date >= '2021-10-10' AND due_date < '2021-11-10'
THEN balance_amount ELSE 0 END) AS 1month,
SUM(CASE WHEN due_date >= '2021-09-10' AND due_date < '2021-10-10'
THEN balance_amount ELSE 0 END) AS 2month
FROM invoices
WHERE
status = 'ACTIVE' AND balance_amount > 0 AND deleted_at IS NULL
GROUP BY
company_id;
推荐阅读
- javascript - 避免在 div 中嵌入媒体以通过 innerHTML 添加刷新
- excel - 具有多个条件和复选框的条件格式
- django - Django迁移不持久
- javascript - 提示显示脚本无法重置为默认内容
- scala - 如何在scala中更改集合的元素
- javascript - 在 React.js 中从 list1 复制到 list2 时需要帮助以避免重复数据
- python - 从 HTML 表中抓取足球数据
- docker - 如何避免在 dockerhub 上上传基础镜像?
- c# - 如果我使用 ControlTemplate,WPF ToggleButton 在检查时消失
- javascript - Vue Router 只应用一次活动类,不再应用