如何在SQL的同一列中比较两个日期

萨拉·莫拉迪（Sara Moradi）

我必须比较它们在表的一列中的两个日期，我需要进行比较以找到所需的特定日期之前和之后的日期，还必须在3个不同的列中显示它们

我写了这段代码，但这是完全错误的：

CREATE VIEW
AS
SELECT (CASE 
        WHEN T1.BuyDate > T2.BuyDate THEN T1.BuyDate END)
AS PreviousBuyDate, T1.ItemName, T1.BuyDate,  
(CASE 
        WHEN T1.BuyDate > T2.BuyDate THEN T1.BuyDate END)
AS  NextDate
FROM FoodSara_tbl T1 , FoodSara_tbl T2
GO

输入：

|ItemName | BuyDate | ItemOrigin |
|---------|---------|------------|
| cake    |2020-10-2| UK         |
| coca    |2020-5-2 | US         |
| cake    |2019-10-6| UK         |
| coca    |2020-12-2| US         |

输出：

|PreviousDate | ItemName | BuyDate |NextDate |
|-------------|----------|---------|---------|
| NULL        |cake      |2019-10-6|2020-10-2|
| NULL        |coca      |2020-5-2 |2020-12-2|
|2019-10-6    |cake      |2020-10-2| NULL    |
| 2020-5-2    |coca      |2020-12-2| NULL    |

PS：我得安排个约会。

哈桑·法蒂

尝试使用以下LAG功能：

select LAG(BuyDate,1) OVER (PARTITION BY ItemName ORDER BY BuyDate asc) previous_date 
     , ItemName
     , BuyDate
     , LAG(BuyDate,1) OVER (PARTITION BY ItemName  ORDER BY BuyDate desc) next_date
from FoodSara_tbl

查看最终结果：sqlfiddle

要么

用途LAG与LEAD功能：

select LAG(BuyDate,1) OVER (PARTITION BY ItemName order by BuyDate) previous_date 
     , ItemName
     , BuyDate
     , LEAD(BuyDate,1) OVER (PARTITION BY ItemName order by BuyDate) next_date
from FoodSara_tbl

查看最终结果；sqlfiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。