SQLServer数据库获如何取重复记录中日期最新的记录：SQLServer数据库获如何取重复记录中日期最新的记录
SQLServer数据库获如何取重复记录中日期最新的记录：SQLServer数据库获如何取重复记录中日期最新的记录[user_id] [int] NULL[expense_time] [datetime] NULL[id] [int] IDENTITY(1 1) NOT NULL[goods_id] [int] NULL[amount] [decimal](18 6) NULL
在日常的项目开发当中，经常会遇到获取同一属性相同的记录，如何获取记录时间最新的那一条，比如获取某个淘宝用户最新一次的购物记录，美团外卖获取用户最后一次的点外卖记录等等场景，下面通过简单的示例给大家提供三种比较常见的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查询结果如下图：
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;
最终执行结果如图：
