在日常的项目开发当中，经常会遇到获取同一属性相同的记录，如何获取记录时间最新的那一条，比如获取某个淘宝用户最新一次的购物记录，美团外卖获取用户最后一次的点外卖记录等等场景，下面通过简单的示例给大家提供三种比较常见的SQL写法，希望能给大家带来一些思路。
1、建表脚本：记录的是当前用户的订单记录。如果某个人多次下单会出现某个人重复下订单的记录。脚本如下：
CREATE TABLE [dbo].[t\_expense\_record\_info](
[id] [int] IDENTITY(1,1) NOT NULL,
[goods\_id] [int] NULL,
[amount] [decimal](18, 6) NULL,
[expense\_time] [datetime] NULL,
[user\_id] [int] NULL,
[create\_date] [datetime] NULL,
CONSTRAINT [PK\_t\_expense\_record\_info] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD\_INDEX = OFF, STATISTICS\_NORECOMPUTE = OFF, IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS = ON, ALLOW\_PAGE\_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY\_INSERT [dbo].[t\_expense\_record\_info] ON
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (1, 100, CAST(5000.000000 AS Decimal(18, 6)), CAST(0x0000A9D900CDFE60 AS DateTime), 1, CAST(0x0000AB4700000000 AS DateTime))
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (2, 100, CAST(2000.000000 AS Decimal(18, 6)), CAST(0x0000AB460130DEE0 AS DateTime), 2, CAST(0x0000AB4700000000 AS DateTime))
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (3, 118, CAST(300.000000 AS Decimal(18, 6)), CAST(0x0000AB430130DEE0 AS DateTime), 1, CAST(0x0000AB4700000000 AS DateTime))
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (4, 20, CAST(1500.000000 AS Decimal(18, 6)), CAST(0x0000AB480130DEE0 AS DateTime), 2, CAST(0x0000AB4700000000 AS DateTime))
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (5, 300, CAST(100.000000 AS Decimal(18, 6)), CAST(0x0000AA860130DEE0 AS DateTime), 3, CAST(0x0000AB4700000000 AS DateTime))
INSERT [dbo].[t\_expense\_record\_info] ([id], [goods\_id], [amount], [expense\_time], [user\_id], [create\_date]) VALUES (6, 80, CAST(7000.000000 AS Decimal(18, 6)), CAST(0x0000AAD5013BDB60 AS DateTime), 1, CAST(0x0000AB4700000000 AS DateTime))
SET IDENTITY\_INSERT [dbo].[t\_expense\_record\_info] OFF
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'商品id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'goods\_id'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'消费金额' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'amount'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'消费时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'expense\_time'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'消费者id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'user\_id'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info', @level2type=N'COLUMN',@level2name=N'create\_date'
GO
EXEC sys.sp\_addextendedproperty @name=N'MS\_Description',
@value=N'消费记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't\_expense\_record\_info'
GO
SELECT * FROM t\_expense\_record\_info ORDER BY user\_id;
执行SQL查询结果如下图：
方法1：
SELECT a.* FROM t\_expense\_record\_info a
LEFT JOIN t\_expense\_record\_info b ON a.user\_id = b.user\_id AND a.expense\_time < b.expense\_time
WHERE b.id IS NULL ORDER BY a.user\_id;
方法2：
select * from t\_expense\_record\_info a
where a.expense\_time in
(select max(b.expense\_time) from t\_expense\_record\_info b where b.user\_id=a.user\_id)
ORDER BY a.user\_id;
方法3：
select * from t\_expense\_record\_info a where not exists
(select 1 from t\_expense\_record\_info b where b.user\_id=a.user\_id and b.expense\_time>a.expense\_time)
ORDER BY a.user\_id;
最终执行结果如图：
