/******************************************************************************************************************************************************
1、Num、
Name
相同的重复值记录,没有大小关系只保留一条
2、
Name
相同,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
方法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
)
方法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
生成结果:
/*
ID
Name
Memo
1 A A1
4 B B1
(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
max
(ID)ID,
Name
from
#T
group
by
Name
) b
on
a.
Name
=b.
Name
and
a.ID=b.ID
order
by
ID
方法3:
select
*
from
#T a
where
ID=(
select
max
(ID)
from
#T
where
Name
=a.
Name
)
order
by
ID
方法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
max
(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
desc
)
方法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
max
(ID)
from
#T
group
by
Name
)
方法10:
select
ID,
Name
,Memo
from
(
select
*,
max
(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
desc
)
as
MinID
from
#T a)T
where
MinID=1
生成结果2:
/*
ID
Name
Memo
3 A A3
5 B B2
(2 行受影响)
*/
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
方法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 行受影响)
*/
方法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 行受影响)
*/
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
#
select
*
from
#T
/*
Num
Name
1 A
2 B
(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:
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 行受影响)
*/