时间戳

首页 > 解决方案 > 在 SQL Server 上报告以前的记录

sql-server - 在 SQL Server 上报告以前的记录

问题描述

我在这里有点挣扎。数据是捏造的，但查询的概念很真实。我需要选择客户、当前金额、先前金额、序列和日期，其中日期 < 1190105 并且日期/序列是客户在该日期点分组之前的最大日期/序列。

我已经花了好几天的时间尝试使用 HAVING、嵌套选择来尝试获取客户的最大日期/金额和最小日期/金额，但我无法完全理解它。我相信这应该很容易，但是您可以提供的任何帮助将不胜感激。

谢谢

**SEQ       DATE        CUSTOMER    AMOUNT**
1           1181225     Bob         400
2           1181226     Fred        300
3           1190101     Bob         100
4           1190104     Fred        500
5           1190104     George      200
6           1190105     Bob         150
7           1190106     Bob         200
8           1190110     Fred        160
9           1190110     Bob         300
10          1190112     Fred        400

标签： sql-server

解决方案

选项 1 使用行号和滞后函数

SELECT 
    ROW_NUMBER() OVER (Partition By CustomerID Order By [Date]) as Sec,
    [Date],
    Customer,
    Amount as CurrentAmount,
    Lead(Amount) OVER (Partition By CustomerID, Order By [Date]) as PreviousAmount
FROM
    YourTable
WHERE
    [DATE] < 1190105

选择使用外部应用

SELECT 
    ROW_NUMBER() OVER (Partition By Customer Order By [Date]) as Sec,
    [Date],
    Customer,
    Amount as CurrentAmount,
    Prev.Amount  as PreviousAmount
FROM
    YourTable T
OUTER APPLY (
    SELECT TOP 1 Amount FROM YourTable 
    WHERE Customer = T.Customer AND [Date] < T.[Date]
    ORDER BY [DATE] DESC
) Prev
WHERE
   DATE < 1190105

选项 3 使用相关子查询

SELECT 
    ROW_NUMBER() OVER (Partition By Customer Order By [Date]) as Sec,
    [Date],
    Customer,
    Amount as CurrentAmount,
    (
        SELECT TOP 1 Amount FROM YourTable 
        WHERE Customer = T.Customer AND [Date] < T.[Date]
        ORDER BY [DATE] DESC
    )  as PreviousAmount
FROM YourTable
WHERE
    DATE < 1190105

推荐阅读