如何按人获取不同列的最旧和最新记录 - SQL Server

Question

>我需要在交易表中获取每个人之前最早的余额和最新的实际余额。

例如，我得到了这个：

+-----------------+---------------+-----------------+---------------+
| dateTransaction | idPerson      | previousBalance | actualBalance |
+-----------------+---------------+-----------------+---------------+
| 01-11-2017      |      1        |       0         |    1000       |
| 02-11-2017      |      2        |       500       |    2000       |
| 03-11-2017      |      1        |       1000      |    1500       |
| 04-11-2017      |      2        |       2000      |    2500       |
+-----------------+---------------+-----------------+---------------+

所以我需要得到这样的东西：

+---------------+-----------------+---------------+
| idPerson      | previousBalance | actualBalance |
+---------------+-----------------+---------------+
|      1        |       0         |    1500       |
|      2        |       500       |    2500       |
+---------------+-----------------+---------------+

这是创建表的脚本：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaction]
(
    [numberTransaction] [int] NOT NULL,
    [dateTransaction] [datetime] NOT NULL,
    [idPerson] [decimal](20, 0) NOT NULL,
    [transactionAmount] [decimal](18, 3) NOT NULL,
    [previousBalance] [decimal](18, 3) NOT NULL,
    [actualBalance] [decimal](18, 3) NOT NULL,
    CONSTRAINT [PK_Transaction] 
        PRIMARY KEY CLUSTERED ([numberTransaction] ASC)
) ON [PRIMARY]
GO

提前感谢...

Answer 1

按如下方式使用row_number() over()：

SELECT *
FROM (
    SELECT t.*
    , row_number() over(partition by idPerson order by dateTransaction ASC) as is_oldest
    , row_number() over(partition by idPerson order by dateTransaction DESC) as is_newest
    FROM Transaction t
     ) d
WHERE ( is_oldest = 1 OR is_newest = 1 )
ORDER BY idPerson, dateTransaction ASC

在over clause中，partition by表示row_number()分配的号码将是 PER idPerson。然后，order by在最早的日期(排序为发送时(设置 1，在最新的日期(降序排序时(设置 1。