删除重复数据。解决立马结帖

happy664618843 2016-12-13 06:02:11

我想根据ForWichApplication ServerName InstanceName DBName这四个分组.
删除数据保留最大ID

最终需要结果如下:

33697 SQL_Monitoring cnshjssql17 Dcinstance SQLMonitorMeta 32832.00 245432.00 2016-07-25 17:48:25.973
34002 SQL_Monitoring cnshjssql17 Dcinstance SQLMonitorMeta 279616.00 2360.00 2016-07-25 17:48:27.350

...全文

143 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

学海无涯-回头是岸 2016-12-14

打赏
举报


WITH T1 AS(
SELECT ForWichApplication,	ServerName,	InstanceName,	DBName  MAX(REPORTID)AS IID FROM A )
SELECT A.* INTO NEWTABLE FROM A,T1 WHERE A.ForWichApplication=T1.ForWichApplication AND A.ServerName=T1.ServerName
AND A.InstanceName=T1.InstanceName AND A.DBName=T1.DBName AND A.REPORTID=T1.IID
--用 删除也可以

1#的方法也很好

LongRui888 2016-12-14

打赏
举报

引用 6 楼 happy664618843 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 试试这个： ;with t as ( select *,row_number() over(partition by ForWichApplication,ServerName,InstanceName,DBName order by ReportID desc) rn from tb )t delete from t where rn > 1

你好！我这是2008, 用你这方法直接报错。 Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 't'.[/quote] 改一下： ;with t as ( select *,row_number() over(partition by ForWichApplication,ServerName,InstanceName,DBName order by ReportID desc) rn from tb ) delete from t where rn > 1

happy664618843 2016-12-14

打赏
举报

引用 1 楼 yupeigu 的回复:

试试这个： ;with t as ( select *,row_number() over(partition by ForWichApplication,ServerName,InstanceName,DBName order by ReportID desc) rn from tb )t delete from t where rn > 1

你好！我这是2008, 用你这方法直接报错。 Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 't'.

canglanzhixia 2016-12-13

打赏
举报

引用 3 楼 canglanzhixia的回复:

select * from tableName where exists (select * from tableName a where a.id>tableName.id)

这个是查询。删除的话同理。您可以尝试一下。

canglanzhixia 2016-12-13

打赏
举报

select * from tableName where exists (select * from tableName a where a.id>tableName.id)

happy664618843 2016-12-13

打赏
举报

引用 1 楼 yupeigu 的回复:

试试这个： ;with t as ( select *,row_number() over(partition by ForWichApplication,ServerName,InstanceName,DBName order by ReportID desc) rn from tb )t delete from t where rn > 1

谢谢！明天上班我试下！

LongRui888 2016-12-13

打赏
举报

试试这个： ;with t as ( select *,row_number() over(partition by ForWichApplication,ServerName,InstanceName,DBName order by ReportID desc) rn from tb )t delete from t where rn > 1