【SQL语句极度挑战】求一高难度删除SQL语句

[0轰隆隆0]

现有表 temp

结构： UserID varchar(20);
OperateDate datetime;

[其它字段略,无主键]


数据： UserID OperateDate
-------- ------------------------
aaa 2008-04-02 14:58:13.073
bbb 2008-04-02 19:28:17.307
aaa 2008-04-02 19:28:43.870
ccc 2008-04-03 12:45:54.353
bbb 2008-04-03 12:46:35.557
aaa 2008-04-04 00:56:23.290
ddd 2008-04-06 00:10:04.887
aaa 2008-04-06 04:25:31.997

要求： 删除重复的数据
先根据 UserID 判断某条重复的记录是否大于5条,如果大于5条，则根据OperateDate 进行排序，删除日
期最早的记录，保留最新的重复UserID 的5条记录，所有UserID的重复记录可以有重复，但不允许超
过6条，只允许保留5条

补充：如果上面的要求不是看的很明白的话，那我换一个方向说一下，当执行了语句之后，在这张表里面，aaa的记录
不允许出现6条，bbb的记录也不允许出现6条；假如有一条记录aaa 的重复出现了8次，那么将删除这8条记录
中的3条，而且这三条是时间最早的三条，保留最新的5条。


阐述完毕，谢谢

[0轰隆隆0]

沙发自己坐..

[fcuandy]

20分挑不过

[让你望见影子的墙]

--> --> (Ben)生成測試數據
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb(UserID varchar(10),	OperateDate datetime)

Insert tb
select N'aaa','2008-04-02 14:58:13.073' union all
select N'aaa','2008-04-02 19:28:17.307' union all
select N'aaa','2008-04-02 19:28:43.870' union all
select N'aaa','2008-04-03 12:45:54.353' union all
select N'aaa','2008-04-03 12:46:35.557' union all
select N'aaa','2008-04-04 00:56:23.290' union all
select N'aaa','2008-04-06 00:10:04.887' union all
select N'aaa','2008-04-06 04:25:31.997'
Go
Select * from tb order by operatedate

delete from tb
where userid in (select userid from tb group by userid having count(*)>5)
     and operatedate in(select top 3 operatedate from tb where userid in (select userid from tb group by userid having count(*)>5) order by operatedate)
select * from tb

结果：

aaa	2008-04-03 12:45:54.353
aaa	2008-04-03 12:46:35.557
aaa	2008-04-04 00:56:23.290
aaa	2008-04-06 00:10:04.887
aaa	2008-04-06 04:25:31.997

[让你望见影子的墙]

有点理解错了，改一下


delete from tb
where userid in (select userid from tb group by userid having count(*)>5)
and operatedate not in(select top 5 operatedate from tb where userid in (select userid from tb group by userid having count(*)>5) order by operatedate desc)
select * from tb

[-晴天]

delete from temp a where 
userid in(
select userID from temp group by userid having count(userid)>5
) and operatedate not in(
select top 5 operatedate from temp where userid=a.userid order by operatedate desc
)

[arrow_gx]

--先查询出来看看结果是否正确
select * from t1 where UserID in (
select UserID from t1 group by UserID
having exists (select UserID from t1 group by UserID having count(1)>5)
and OperateDate=min(OperateDate) )

--然后进行删除操作
delete from t1 group by UserID
having exists (select UserID from t1 group by UserID having count(1)>5)
and OperateDate=min(OperateDate)

[0轰隆隆0]

谢谢支持


不在分数，贵在挑战

[fcuandy]

不是分数，看到这种标题，有点怕。

[一品梅]

楼主在南极?

[cxmcxm]

delete a
from temp a where not exists(select * from (select top 5 * from temp where userid=a.userid order by operatedate desc) b where operatedate=a.operatedate)

[mjjzg]

等待

[comszsoft]

标题党