每個日期、開始和結束的行

我有一個有兩列的表，startDate並且endDate. 每一天都有自己的容量。在這些日期之間，使用者可以獲得 1 到 n 個項目。我將獲取每天剩餘的項目數量並將它們展示給使用者。有什麼方法可以通過一個查詢來處理嗎？

我正在使用spitett/php-mysql docker映像，並且 MySQL 版本是 5.5。

id    startDate     endDate      capacity
----------------------------------------------------
1     2019-05-01     2019-06-01     8
2     2019-06-21     2019-07-01     13
3     2019-07-11     2019-08-01     4

另一個表儲存使用者數據

id        user_id      item_id      date                number
----------------------------------------------------------------
1             1            1       2019-05-03             1
2             2            3       2019-07-13             4

期望的輸出：

item_id            date           available_items
-------------------------------------------------------
1                  2019-05-01           8
1                  2019-05-02           8
1                  2019-05-03           7 <<-----------
1                  2019-05-04           8
1                  2019-05-05           8
...
1                  2019-05-31           8
2                  2019-06-21           13
2                  2019-06-22           13
...
2                  2019-06-30           13
3                  2019-07-11           4
3                  2019-07-12           4
3                  2019-07-13           0 <<-----------
3                  2019-07-14           4
...
3                  2019-07-31           4

我建議您創建一個包含幾年數據的日曆表。對於您的範例，我將只使用一個具有足夠多行的行：

CREATE TABLE CALENDAR (d date not null primary key);
INSERT INTO CALENDAR (d) VALUES ('2019-05-01'),...,('2019-08-01');

在此過程中，讓我們創建一些可用的範例數據而不是 ascii-art 表：

create table items 
( item_id int not null primary key
, startdate date not null
, enddate date not null
, capacity int not null);

insert into items (item_id, startdate, enddate, capacity)
values (1, '2019-05-01', '2019-06-01', 8)
    , (2, '2019-06-21', '2019-07-01', 13)
    , (3, '2019-07-11', '2019-08-01', 4);

要確定每個日期的容量：

select cal.d, i.item_id, coalesce(i.capacity, 0) as capacity
from calendar cal
left join items i
   on cal.d between i.startdate and i.enddate;

現在您可以從中減去您的使用者數據，也許最容易使用子選擇

select cal.d, i.item_id
    , coalesce(i.capacity, 0) - coalesce((select sum(number)
                                          from userdata u
                                          where u.dt = cal.d
                                            and u.item_id = i.item_id),0) as availible_items
from calendar cal
left join items i
   on cal.d between i.startdate and i.enddate;

引用自：https://dba.stackexchange.com/questions/239768