删除重复vipid并且point字段积分小的删除掉。

Rotel-刘志东 2009-09-22 02:39:00

VipId Point
1315020 132
1315020 100
1315098 14
1315098 1
1315283 2340
1315283 914
1315370 5775
1315370 5141
1315523 726
1315523 16

...全文

94 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

navy887 2009-09-22

打赏
举报

DELETE tb FROM tb t WHERE Point in (select Min(Point) from tb WHERE VipId = t.VipId )

--小F-- 2009-09-22

打赏
举报

http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?73339

楼主可以参考下

--小F-- 2009-09-22

打赏
举报

----------------------------------------------------------------

-- 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 行受影响)

*/

SQL77 2009-09-22

打赏
举报

--> 测试数据：[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 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 行）

SQL77 2009-09-22

打赏
举报

DELETE TB FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE VipId =T.VipId AND POINT>T.POINT)

xiequan2 2009-09-22

打赏
举报

--> 测试数据：[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



select * 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 行受影响)





*/

billpu 2009-09-22

打赏
举报

[Quote=引用 1 楼 dawugui 的回复:]
drop tb from tb t where Point not in (select max(Point) from tb where VipId = t.VipId )
[/Quote]

delete tb ...

dawugui 2009-09-22

打赏
举报

create table tb(VipId varchar(10) , Point int)
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)
go

delete 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 行）
*/

dawugui 2009-09-22