VipId Point
1315020 132
1315020 100
1315098 14
1315098 1
1315283 2340
1315283 914
1315370 5775
1315370 5141
1315523 726
1315523 16
1315020 132
1315020 100
1315098 14
1315098 1
1315283 2340
1315283 914
1315370 5775
1315370 5141
1315523 726
1315523 16
insert into tb values('1315020', 132 )
insert into tb values('1315020', 100 )
insert into tb values('1315098', 14 )
insert into tb values('1315098', 1 )
insert into tb values('1315283', 2340)
insert into tb values('1315283', 914 )
insert into tb values('1315370', 5775 )
insert into tb values('1315370', 5141 )
insert into tb values('1315523', 726 )
insert into tb values('1315523', 16)
godelete tb from tb t where Point not in (select max(Point) from tb where VipId = t.VipId )select * from tb
drop table tb/*VipId Point
---------- -----------
1315020 132
1315098 14
1315283 2340
1315370 5775
1315523 726(所影响的行数为 5 行)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([VipId] int,[Point] int)
insert [tb]
select 1315020,132 union all
select 1315020,100 union all
select 1315098,14 union all
select 1315098,1 union all
select 1315283,2340 union all
select 1315283,914 union all
select 1315370,5775 union all
select 1315370,5141 union all
select 1315523,726 union all
select 1315523,16select * from [tb]delete a from tb a where exists (select 1 from tb where [VipId]=a.[VipId] and [Point]>a.[Point] )select * from [tb]/*
VipId Point
----------- -----------
1315020 132
1315020 100
1315098 14
1315098 1
1315283 2340
1315283 914
1315370 5775
1315370 5141
1315523 726
1315523 16(10 行受影响)(5 行受影响)VipId Point
----------- -----------
1315020 132
1315098 14
1315283 2340
1315370 5775
1315523 726(5 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([VipId] int,[Point] int)
insert [tb]
select 1315020,132 union all
select 1315020,100 union all
select 1315098,14 union all
select 1315098,1 union all
select 1315283,2340 union all
select 1315283,914 union all
select 1315370,5775 union all
select 1315370,5141 union all
select 1315523,726 union all
select 1315523,16DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE VipId =T.VipId AND POINT>T.POINT)SELECT * FROM TB(所影响的行数为 10 行)
(所影响的行数为 5 行)VipId Point
----------- -----------
1315020 132
1315098 14
1315283 2340
1315370 5775
1315523 726(所影响的行数为 5 行)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-22 14:46:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([VipId] int,[Point] int)
insert [tb]
select 1315020,132 union all
select 1315020,100 union all
select 1315098,14 union all
select 1315098,1 union all
select 1315283,2340 union all
select 1315283,914 union all
select 1315370,5775 union all
select 1315370,5141 union all
select 1315523,726 union all
select 1315523,16
--------------开始查询--------------------------
delete a from tb a where exists (select 1 from tb where [VipId]=a.[VipId] and [Point]>a.[Point] )select * from [tb]----------------结果----------------------------
/* VipId Point
----------- -----------
1315020 132
1315098 14
1315283 2340
1315370 5775
1315523 726(5 行受影响)
*/