如何仅选择日期范围中包含列值0的ID

Question

我有一个类似于的表

VALID_FROM | VALID_TO   | id | stock
2020-10-01 | 2020-10-02 | 1  | 10
2020-10-02 | 2020-10-04 | 1  | 9
2020-10-04 | 2020-10-08 | 1  | 5
2020-11-12 | 2020-11-26 | 1  | 4
...        | ...        | 1  |
2020-12-15 | 2020-12-16 | 1  | 0
2020-10-01 | 2020-10-02 | 2  | 10
2020-10-02 | 2020-10-04 | 2  | 9
2020-10-04 | 2020-10-08 | 2  | 5
...        | ...        | 2  |
2020-12-15 | 2020-12-16 | 2  | 1
2020-11-12 | 2020-11-26 | 3  | 13
...        | ...        | 3  |
2020-11-30 | 2020-11-30 | 3  | 0

我通过过滤特定日期范围

SELECT *
FROM table AS t
WHERE t.VALID_FROM >= ADD_DAYS('2020-11-26', -14)
AND t.VALID_TO <= ADD_DAYS('2020-11-26', +14)

但现在我需要找到每一个"；id"；达到"；股票；在该日期范围内为0。

根据这个结果，我需要条目：

1. id
2. ADD_DAYS日期的库存("2020-11-26"，-14(-此处为"2020-11-12">
3. 股票达到0的日期

所以结束表应该是这样的：

id | stock_at_date-14days | day_of_stock_reaching_0
1  | 4                    | 2020-12-16
3  | 13                   | 2020-11-30

如何使用SQL实现这一点？

Answer 1

尝试自联接。

SELECT t.*, t0.*
FROM table AS t
JOIN table AS t0 ON t.id = t0.Id
AND t.VALID_FROM = ADD_DAYS('2020-11-26', -14)
AND t0.Stock = 0 
AND t0.VALID_FROM <= ADD_DAYS('2020-11-26', -14)
AND t0.VALID_TO <= ADD_DAYS('2020-11-26', +14)

Answer 2

您可以使用join:

SELECT t.*, t2.stock as days_before_14
FROM table t LEFT JOIN
table t2
ON t.id = t2.id AND
ADD_DAYS(t.VALID_FROM, -14) >= t2.VALID_FROM AND
ADD_DAYS(t.VALID_FROM, -14) < t2.VALID_TO AND        
WHERE t.VALID_FROM >= ADD_DAYS('2020-11-26', -14) AND
t.VALID_TO <= ADD_DAYS('2020-11-26', +14) AND
t.stock = 0;