34,575
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 09:21:45
-- 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]([id] int,[name] varchar(3),[no] varchar(2))
insert [tb]
select 1,'aaa','22' union all
select 2,'aaa','22' union all
select 3,'bbb','12' union all
select 4,'bbb','12' union all
select 5,'bbb','12' union all
select 6,'ccc','33' union all
select 7,'ddd','gg'
--------------开始查询--------------------------
delete t from tb t where exists(select 1 from tb where t.no=no and t.Name=Name and t.id<id)
select * from tb
----------------结果----------------------------
/* id name no
----------- ---- ----
2 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg
(4 行受影响)
*/
delete from tb t
where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id<id)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 09:21:45
-- 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]([id] int,[name] varchar(3),[no] varchar(2))
insert [tb]
select 1,'aaa','22' union all
select 2,'aaa','22' union all
select 3,'bbb','12' union all
select 4,'bbb','12' union all
select 5,'bbb','12' union all
select 6,'ccc','33' union all
select 7,'ddd','gg'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where name=t.name and no=t.no and id>t.id )
----------------结果----------------------------
/* id name no
----------- ---- ----
2 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg
(4 行受影响)
*/
create table tb(id int,name varchar(10),no varchar(10))
insert into tb values(1 , 'aaa' , '22')
insert into tb values(2 , 'aaa' , '22')
insert into tb values(3 , 'bbb' , '12')
insert into tb values(4 , 'bbb' , '12')
insert into tb values(5 , 'bbb' , '12')
insert into tb values(6 , 'ccc' , '33')
insert into tb values(7 , 'ddd' , 'gg')
go
delete tb from tb t where id not in (select min(id) from tb where no = t.no)
select * from tb
drop table tb
/*
id name no
----------- ---------- ----------
1 aaa 22
3 bbb 12
6 ccc 33
7 ddd gg
(所影响的行数为 4 行)
*/
delete from tb t
where not exists(select 1 from tb where t.name=Name and t.no=no and t.id>id)
delete from tb t
where not exists(select 1 from tb where t.name=no and t.Name=Name and t.id>id)
SELECT * FROM TB T WHERE ID=(SELECT MIN(ID) FROM TB WHERE NAME=T.NAME AND NO=T.NO)
select * from tb t where not exists(select 1 from tb where name=t.name and no=t.no and id>t.id)