每个日期的SQL总金额 [英] SQL sum amount for each date

问题描述

我有一个名为"rentals"的表，其中存储了如下数据:

I have a table called "rentals" in which I store data like the following:

id | rent_id | start_date | end_date  | amount
---------------------------------------------
1  |   54    | 12-10-2019 | 26-10-2019| 100
2  |   54    | 13-10-2019 | 20-10-2019| 150

我期望什么?结果如下:

What do I expect? A result like:

12-10-2019 , amount 100
from 13-10-2019 to 20-10-2019, amount 250
from 21-10-2019 to 26-10-2019, amount 100

基本上，我每天想要的是金额的总和.但是我也想计算之间的天数".

Basically I want, for each day , the sum of amount. But I also want to calculate "days between".

所以预期结果将是:

    id | rent_id | day        |  amount
    ---------------------------------------------
    1  |   54    | 12-10-2019 | 100
    2  |   54    | 13-10-2019 | 250
    3  |   54    | 14-10-2019 | 250

以此类推...

我实际上正在运行以下sql:

I'm actually running the following sql:

select start_date, ( select sum(amount) from rentals as t2 where t2.start_date <= t1.start_date) as amount from rentals as t1 WHERE rent_id = 54 group by start_date

但结果与预期不符...

but the result is not like expected...

我正在使用MySQL.

I'm using MySQL.

推荐答案

此问题的常见解决方案包括首先创建日历表.该表存储了查询需要处理的所有可能的日期.有一些可用于创建和填写表格的解决方案，例如，您可以查看

A common solution to this question involves first creating a calendar table. That's a table that stores all possible dates that your query needs to deal with. There are solutions available in the wild to create and fill the table, for example you can have a look at this SO post.

因此，我们假设一个具有以下结构和数据的表:

So let's assume a table with the following structure and data:

CREATE TABLE all_dates as (my_date DATE PRIMARY KEY);
INSERT INTO all_dates VALUES
    ('12-10-2019'),
    ('13-10-2019'),
    ('14-10-2019'),
    ...
;

现在，只需将表与日历表连接起来并汇总结果即可解决问题，如下所示:

Now your question can be solved simply by joining your table with the calendar table and aggregating the results, as follows:

SELECT r.rent_id, d.my_date `day`, SUM(amount) amount
FROM all_dates d
INNER JOIN rentals r ON d.my_date BETWEEN r.start_date AND r.end_date
GROUP BY r.rent_id, d.my_date

注意:您对生成的 id 列的意图不清楚.您似乎愿意创建一个新的唯一ID.如果是这样，一种选择是使用 ROW_NUMBER()，如下所示:

Note: your intent for the resulting id column is unclear. You seem to be willing to create a new unique id. If so, one option would be to use ROW_NUMBER(), as follows:

SELECT ROW_NUMBER() OVER(ORDER BY rent_id, `day`) id, x.*
FROM (
    SELECT r.rent_id, d.my_date `day`, SUM(amount) amount
    FROM all_dates d
    INNER JOIN rentals r ON d.my_date BETWEEN r.start_date AND r.end_date
    GROUP BY r.rent_id, d.my_date
) x

这篇关于每个日期的SQL总金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！