下面是Invoices表格：
我正在嘗試進行一個 sql 查詢，它根據due_date范圍和balance_amountgroup 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`
但它給了我錯誤的余額數字。
uj5u.com熱心網友回復：
我建議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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/355168.html
上一篇：將TEXT轉換為日期('yyyy-mm-dd')格式時輸出不正確(Postgresql)
下一篇：在空間表中查找城市之間的距離