27,582
社区成员
发帖
与我相关
我的任务
分享3个字段 去重问题啊
如图 ,WxWorkId, UserName, addDate 取年月日
如果这3个重复 则 update isdelete =1
不会写sql 语句了
...全文
请发表友善的回复…
发表回复
砸死牛顿的苹果 2017-11-26
- 打赏
- 举报
如图 ,WxWorkId, UserName, addDate 取年月日
如果这3个重复 则 update isdelete =1
update a
set isdelete =1
from tb a
where exists
(select 1 from tb b
where a.WxWorkId=b.WxWorkId
and a.UserName=b.UserName
and cast(a.addDate as date)=cast(b.addDate as date)
and a.id>b.id
)
日月路明 2017-11-26
- 打赏
- 举报
update table-A
set IsDelete=1
from Table-A a
join(select ID=min(ID), WxWorkId, UserName, iDay=DateDiff(Day,0,addDate)
from Table-A
group by WxWorkId, UserName, DateDiff(Day,0,addDate)
) as b on a.WxWorkId=b.WxWorkId and a.UserName=b.UserName and DateDiff(Day,0,a.addDate)=b.iDay and a.ID>b.ID
w87875251l 2017-11-25
- 打赏
- 举报
注意 AddDate是取年月日 保留 Id最小的那个 删除其余重复的
