SQLServer数据库获取重复记录中日期最新的记录

一、SQLServer数据库获取单表重复记录中日期最新的记录

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;



--方法一

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;

--方法二

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;

--方法三
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;

 单表去重前

  预期效果单表去重后

SQL 通过连接两个位于不同服务器上的数据库表查询数据|极客笔记

https://www.cnblogs.com/bingege/archive/2012/04/13/2446198.html

二、你 MySQL 中重复数据多吗，教你一招优雅的处理掉它们！

在需要保证数据唯一性的场景中，个人觉得任何使用程序逻辑的重复校验都是不可靠的，这时只能在数据存储层做唯一性校验。MySQL 中以唯一键保证数据的唯一性，那么若新插入重复数据时，我们可以让 MySQL 怎么来处理呢？

MySQL 支持 3 种数据重复时的原子操作，下面结合示例进行说明。示例的表结构为：

你 MySQL 中重复数据多吗，教你一招优雅的处理掉它们！