一个sql语句

[iammac]

一张order表，其中包括order key和order date。
能不能删除其他records只留下unique的order key并且是（按order date排序）最大的记录。

相同order key小于或等于最大order date的记录都删除。相同orderdate就只留下top1的record

e.g

order key order date
1 today
1 today
1 tomorrow
2 today

删除后就剩下
1 today
2 today

[--小F--]

delete  t from tb t where not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

[--小F--]

delete
  t 
from
 tb t 
where
 not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

[NBDBA]

delete a
from order a
where exists (
 select 1 from order
 where [order key] = a.[order key]
 and [order date] > a.[order date]
 )

[NBDBA]

[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])
[/Quote]
你只删除最小的一条了

[iammac]

[Quote=引用 2 楼 fredrickhu 的回复:]

SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])
[/Quote]
not exists [order key]=t.[order key] 不是一条记录都选不到吗？因为自己总是=自己的啊

[--小F--]

[Quote=引用 4 楼 nbdba 的回复:]
引用 2 楼 fredrickhu 的回复:
SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

你只删除最小的一条了
[/Quote]
丫的我2楼改了一下 还是改成和1楼一样了

[--小F--]

delete
  t 
from
 tb t 
where
 exists(select 1 from tb where [order key]=t.[order key] and [order date]>t.[order date])

[iammac]

[Quote=引用 5 楼 iammac 的回复:]

引用 2 楼 fredrickhu 的回复:

SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

not exists [order key]=t.[order key] ……
[/Quote] 不好意思看错了，因为是date...
那orderdate相等的多条记录也需要删除只剩下top1的

[--小F--]

删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)



select * from #T

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID保留最大的一条记录:

方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)

方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)


select * from #T
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/





--3、删除重复记录没有大小关系时，处理重复值


--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#

truncate table #T--清空表

insert #T select * from #    --把临时表#插入到表#T中

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin 
    set rowcount @con;
    delete #T where Num=@Num and Name=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor

--查看结果
select * from #T
/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

[iammac]

[Quote=引用 8 楼 iammac 的回复:]

引用 5 楼 iammac 的回复:

引用 2 楼 fredrickhu 的回复:

SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

not exists [ord……
[/Quote]因为是date... --> 以为是date..

[--小F--]

[Quote=引用 8 楼 iammac 的回复:]
引用 5 楼 iammac 的回复:

引用 2 楼 fredrickhu 的回复:

SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

not exists [orde……
[/Quote]
方法基本全部在这里了 我回答得太匆忙 不好意思

[NBDBA]

[Quote=引用 5 楼 iammac 的回复:]
引用 2 楼 fredrickhu 的回复:

SQL code
delete
t
from
tb t
where
not exists(select 1 from tb where [order key]=t.[order key] and [order date]<t.[order date])

not exists [order key]=t.[order key] 不……
[/Quote]
备份好测试了再下结论

[iammac]

不好意思我之前看错把order key看成order date了。
not exists把比它order小的删除掉，但是如果order date duplicate的情况，需要只剩下top 1的记录。（比如相同key和相同order date只留下top 1的）。
我能想到的是临时表group by key和max(date)，然后inner join主表。
但是这个表还蛮大的(billion的数据量），不知道效率会如何。

[oO寒枫Oo]

表还蛮大的 的话 你先别急着delete 先估计好delete的数量 和你log的空间
这种操作很容易造成 log空间满的
可以考虑 先把你需要保留的数据 select into到temp表
并且重建temp表 定义 主键索引默认值 等
然后 truncate原表 drop 原表

最后改名temp表