SQL 将每日数据合并到同一行(包括新的和已删除的 ID)的最佳方法

Question

我最初认为这会相当简单，但由于某种原因，我正在努力解决这个问题。

如果我有一个看起来像这样的表：

table1
Date        ID       Quantity
2/21        1        100
2/21        2        500
2/21        3        200
2/20        2        600
2/20        3        400
2/20        5        2000

我想连接这些数据，如下所示：

ID        prev_date     prev_quantity    curr_date     curr_quantity
1         2/20          0                2/21          100
2         2/20          600              2/21          500
3         2/20          400              2/21          200
5         2/20          2000             2/21          0

棘手的部分是 ID 1 没有前一天的条目（所以设为 0），ID 5 没有当天的条目（也设为 0）

最好的方法是什么？ 提前感谢！！

Answer 1

这可能会

对您有所帮助,,,,,

SELECT 
    ISNULL(T.ID,T1.ID),
    ISNULL(T.DATE,GETDATE()) AS 'CurrDate',
    ISNULL(T.Quantity,0) AS 'CurrQty',
    ISNULL(T1.DATE,GETDATE()-1) AS 'PrevDate' ,
    ISNULL(T1.Quantity,0) AS 'PrevQty'
FROM 
    (   
        SELECT 
              T.ID,
              T.Quantity,
              T.DATE 
        FROM @TABLE T   
        WHERE T.DATE = CONVERT(VARCHAR,GETDATE(),100)   
    )T
    FULL OUTER JOIN 
    (
      SELECT 
            T1.ID,
            T1.Quantity,
            MAX(T1.DATE) AS [DATE] 
      FROM @TABLE T1 
      WHERE   T1.DATE <> CONVERT(VARCHAR,GETDATE(),100)
      GROUP BY  T1.ID,T1.Quantity
    ) T1 ON T.ID = T1.ID 

Answer 2

SELECT  curr.ID, ISNULL(Prev.date, DATEADD(DD, -1, curr.Date)) AS Prev_Date,        
        ISNULL(prev.Quantity, 0) AS Prev_Quantity, 
        ISNULL(curr.date, DATEADD(DD, 1, prev.Date)) AS Curr_Date, ISNULL(curr.Quantity, 0) AS Curr_Quantity
FROM    table1 curr
            FULL OUTER JOIN
        table1 prev
        ON curr.ID = prev.ID AND prev.Date = DATEADD(DD, -1, curr.Date) 

Answer 3

您可以使用排名来解决差距问题。此外，使用 UNION 代替 ISNULL：

SELECT RANK() OVER (ORDER BY ID,DATE) rank, ID, date, quantity
INTO temp1
FROM (
SELECT date, ID, quantity
          FROM table1
        UNION ALL
        SELECT dateadd(day,-1,MIN(date)) date, ID, 0 quantity
          FROM table1
          GROUP BY ID
        UNION ALL
        SELECT dateadd(day,1,MAX(date)) date, ID, 0 quantity
          FROM table1
          GROUP BY ID
)  AS U;
SELECT p.ID, p.date prev_date, p.quantity prev_quantity, 
             c.date curr_date, c.quantity curr_quantity
FROM temp1 p INNER JOIN temp1 c ON c.rank = p.rank + 1 AND c.ID = p.ID