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),方法3在SQl05时,效率高于1、2 方法1: Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2: select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3: select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4: select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5: select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6: select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7: select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8: select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用): select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10: select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1
Use test GO If object_id('test') Is Not Null Drop table test Go Create Table test ( id int Identity(1,1) Primary Key, x int, y nvarchar(50) ) Insert Into test Select 1,N'a' Union All Select 1,N'a' Union All Select 1,N'a' Union All Select 1,N'a' Union All Select 1,N'c' Union All Select 1,N'c' Union All Select 1,N'c' Union All Select 1,N'd' Union All Select 1,N'd' Union All Select 1,N'd' Union All Select 1,N'e' Union All Select 1,N'e'Go Declare @sql nvarchar(4000) Select @sql=isnull(@sql,'Delete a From test As a Where Exists(Select 1 From test Where a.id>id ')+ ' And '+Quotename(name)+'=a.'+Quotename(name) From syscolumns Where id=object_id('test') And name<>'ID'Exec(@sql+')') Select * From test/* id x y ----------- ----------- ---------- 1 1 a 5 1 c 8 1 d 11 1 e */
1楼比较全,我一般都用 Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
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),方法3在SQl05时,效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID方法11:select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1
GO
If object_id('test') Is Not Null
Drop table test
Go
Create Table test
(
id int Identity(1,1) Primary Key,
x int,
y nvarchar(50)
)
Insert Into test
Select 1,N'a' Union All
Select 1,N'a' Union All
Select 1,N'a' Union All
Select 1,N'a' Union All
Select 1,N'c' Union All
Select 1,N'c' Union All
Select 1,N'c' Union All
Select 1,N'd' Union All
Select 1,N'd' Union All
Select 1,N'd' Union All
Select 1,N'e' Union All
Select 1,N'e'Go
Declare @sql nvarchar(4000)
Select @sql=isnull(@sql,'Delete a From test As a Where Exists(Select 1 From test Where a.id>id ')+
' And '+Quotename(name)+'=a.'+Quotename(name)
From syscolumns
Where id=object_id('test')
And name<>'ID'Exec(@sql+')')
Select * From test/*
id x y
----------- ----------- ----------
1 1 a
5 1 c
8 1 d
11 1 e
*/
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
1楼提供了多种方法,可选一种来处理.
delete t from tb t where exists(select 1 from tb where col1=t.col1 and col2=t.col2 and ... and id>t.id)