590
社区成员
发帖
与我相关
我的任务
分享
userid addtime object
TOM 2017-06-06 会议
TOM 2017-06-06 会议
TOM 2017-06-07 出差
TOM 2017-06-07 出差
TOM 2017-06-07 出差
FAK 2017-06-06 会议
FAK 2017-06-06 会议
FAK 2017-06-06 会议
FAK 2017-06-06 会议
FAK 2017-06-07 出差
FAK 2017-06-07 出差
userid addtime object
TOM 2017-06-06 会议
TOM 2017-06-07 出差
FAK 2017-06-06 会议
FAK 2017-06-07 出差
if not object_id(N'Tempdb..#Tmp_Tbl') is null
drop table #Tmp_Tbl
Go
Create table #Tmp_Tbl
(List_ID int identity(1,1),
userid nvarchar(23),
addtime Date,
object nvarchar(22))
Insert #Tmp_Tbl
select N'TOM','2017-06-06',N'会议' union all
select N'TOM','2017-06-06',N'会议' union all
select N'TOM','2017-06-07',N'出差' union all
select N'TOM','2017-06-07',N'出差' union all
select N'TOM','2017-06-07',N'出差' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-07',N'出差' union all
select N'FAK','2017-06-07',N'出差'
Go
Select *
From #Tmp_Tbl a
Where List_ID=(Select MIN(List_ID) From #Tmp_Tbl b Where b.UserID=a.userid and b.addtime=a.addtime)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([userid] nvarchar(23),[addtime] Date,[object] nvarchar(22))
Insert #T
select N'TOM','2017-06-06',N'会议' union all
select N'TOM','2017-06-06',N'会议' union all
select N'TOM','2017-06-07',N'出差' union all
select N'TOM','2017-06-07',N'出差' union all
select N'TOM','2017-06-07',N'出差' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-06',N'会议' union all
select N'FAK','2017-06-07',N'出差' union all
select N'FAK','2017-06-07',N'出差'
Go
--测试数据结束
SELECT DISTINCT * from #T