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

2008-08-08 04:34:12 181 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

现有表 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条。

阐述完毕，谢谢

...全文

Coze平台智能体开发实战：打造测试工程师的AI助手智能体是具备特定身份和能力的AI实体，由大语言模型（LLM）驱动，并通过插件、知识库和工作流扩展其能力。明确的角色定位：可以设定特定身份和专业领域扩展能力：通过插件连接外部工具和服务记忆能力：通过知识库存储和检索专属信息流程化处理：通过工作流执行复杂多步任务通过Coze平台创建测试工程师智能体，可以显著提升测试效率和质量。明确的角色定位：聚焦测试工程师的实际需求丰富的知识库：提供准确的领域知识支持合适的工具集成：通过插件连接现有工具链持续迭代优化：根据反馈不断改进智能体行为自动化测试生成。阅读详情

0轰隆隆0

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

comszsoft 2008-09-03

打赏
举报

标题党

mjjzg 2008-08-08

打赏
举报

等待

cxmcxm 2008-08-08

打赏
举报

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)

一品梅 2008-08-08

打赏
举报

楼主在南极?

fcuandy 2008-08-08

打赏
举报

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

0轰隆隆0 2008-08-08

打赏
举报

谢谢支持

不在分数，贵在挑战

arrow_gx 2008-08-08

打赏
举报



--先查询出来看看结果是否正确

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)

-晴天 2008-08-08

打赏
举报

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

)

让你望见影子的墙 2008-08-08

打赏
举报

有点理解错了，改一下

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

让你望见影子的墙 2008-08-08

打赏
举报



--> --> (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

fcuandy 2008-08-08

打赏
举报

20分挑不过

0轰隆隆0 2008-08-08

打赏
举报

沙发自己坐..

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

相关推荐