如果找不到产品_成本.开始日期小于产品_收入.开始日期的产品_成本.金额,我如何将产品_成本.金额默认为0?
我在这里用创建脚本和低于https://dbfiddle.uk/PM2nCk5R的查询创建了一把小提琴
查询如下;
SELECT
YEAR(product_revenues.revenue_date) AS revenueYear,
MONTH(product_revenues.revenue_date) AS revenueMonth,
SUM(product_revenues.amount) AS totalRevenue,
COALESCE(SUM(product_costs.amount), 0) AS totalCost,
SUM(product_revenues.amount - COALESCE(product_costs.amount, 0)) AS totalProfit
FROM product_revenues
LEFT JOIN product_costs
ON product_costs.start_date <= product_revenues.revenue_date
AND product_costs.start_date = (
SELECT MAX(start_date)
FROM product_costs
WHERE start_date <= product_revenues.revenue_date
)
WHERE product_revenues.team_id = 1
AND product_costs.team_id =1
AND product_revenues.stripe_product_id ='prod_1234'
AND product_costs.stripe_product_id ='prod_1234'
AND product_revenues.revenue_date BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
GROUP BY revenueMonth, revenueYear
ORDER BY revenueMonth ASC;
这些表格是
产品成本
id | team_id | amount | stripe_product_id | start_date |
---|---|---|---|---|
1 | 1 | 1000 | prod_1234 | 2023-08-01 |
产品收入表
id | team_id | amount | stripe_product_id | revenue_date |
---|---|---|---|---|
1 | 1 | 1000 | prod_1234 | 2023-07-05 |
2 | 1 | 1000 | prod_1234 | 2023-09-01 |
3 | 1 | 1000 | prod_1234 | 2023-10-01 |
预期结果
revenueYear | revenueMonth | totalRevenue | totalCost | totalProfit |
---|---|---|---|---|
2023 | 07 | 1000 | 0 | 1000 |
2023 | 09 | 1000 | 600 | 400 |
2023 | 10 | 1000 | 600 | 400 |
因此,收入id%1没有成本,因为没有早于它的成本记录.但是id 2和3将被归因于成本id 1.我认为Left Join返回所有记录,但我的查询被限制为仅返回2和3收入id.