if not object_id('tb') is null drop table tb Go Create table tb([id] int,[uid] int,[email] nvarchar(3)) Insert tb select 1,6,N'aaa' union all select 2,7,N'bbb' union all select 3,6,N'aaa' union all select 4,8,N'ddd' Go delete t from tb t where exists(select 1 from tb where uid=t.uid and email=t.email and id>t.id) select * from tb /* id uid email ----------- ----------- ----- 2 7 bbb 3 6 aaa 4 8 ddd(3 個資料列受到影響) */
delete a from tb a where exists(select 1 from tb where uid=a.uid and email=a.email and id>a.id)
DECLARE @TB TABLE (id INT, uid INT, email VARCHAR(10)) INSERT @TB SELECT 1 , 6 , 'aaa' UNION ALL SELECT 2 , 7 , 'bbb'UNION ALL SELECT 3 , 6 , 'aaa'UNION ALL SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND ID>T.ID)SELECT * FROM @TB(所影响的行数为 4 行) (所影响的行数为 1 行)id uid email ----------- ----------- ---------- 2 7 bbb 3 6 aaa 4 8 ddd(所影响的行数为 3 行)
DECLARE @TB TABLE (id INT, uid INT, email VARCHAR(10)) INSERT @TB SELECT 1 , 6 , 'aaa' UNION ALL SELECT 2 , 7 , 'bbb'UNION ALL SELECT 3 , 6 , 'aaa'UNION ALL SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND ID<T.ID)SELECT * FROM @TB (所影响的行数为 4 行) (所影响的行数为 1 行)id uid email ----------- ----------- ---------- 1 6 aaa 2 7 bbb 4 8 ddd(所影响的行数为 3 行)
create table test(id int,uid int,email varchar(20)) insert test select 1,6,'aaa' union all select 2,7,'bbb' union all select 3,6,'aaa' union all select 4,8,'ddd' godelete t from test t where exists(select * from test where t.uid = uid and t.email = email and id>t.id)select * from test (所影响的行数为 1 行)id uid email ----------- ----------- -------------------- 2 7 bbb 3 6 aaa 4 8 ddd(所影响的行数为 3 行)
再加个条件,标准点DECLARE @TB TABLE (id INT, uid INT, email VARCHAR(10)) INSERT @TB SELECT 1 , 6 , 'aaa' UNION ALL SELECT 2 , 7 , 'bbb'UNION ALL SELECT 3 , 6 , 'aaa'UNION ALL SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND UID=T.UID AND ID<T.ID)SELECT * FROM @TB(所影响的行数为 4 行) (所影响的行数为 1 行)id uid email ----------- ----------- ---------- 1 6 aaa 2 7 bbb 4 8 ddd(所影响的行数为 3 行)
declare @tb table(id int, uid int, email varchar(3)) insert @tb select 1, 6 , 'aaa' union all select 2 , 7 , 'bbb' union all select 3 , 6 , 'aaa' union all select 4 ,8, 'ddd'delete @tb from @tb t where exists(select * from @tb where uid=t.uid and email=t.email and id<t.id) select * from @tb id uid email ----------- ----------- ----- 1 6 aaa 2 7 bbb 4 8 ddd(3 行受影响)
1 6 aaa ---只留一个
2 7 bbb
3 6 aaa
4 8 ddd ----只留一个要求, 将uid 和email唯一化
求delete sql语句
这个应该是自连接吧,感觉难
and id>t.id)
delete tb
from tb t
where not exists(select * from tb where uid=t.uid and email=t.email and id>t.id)
-->Author:wufeng4552
-->Date :2009-09-15 08:56:03
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[uid] int,[email] nvarchar(3))
Insert tb
select 1,6,N'aaa' union all
select 2,7,N'bbb' union all
select 3,6,N'aaa' union all
select 4,8,N'ddd'
Go
delete t from tb t where exists(select 1 from tb where uid=t.uid and email=t.email
and id>t.id)
select * from tb
/*
id uid email
----------- ----------- -----
2 7 bbb
3 6 aaa
4 8 ddd(3 個資料列受到影響)
*/
a
from
tb a
where
exists(select 1 from tb where uid=a.uid and email=a.email and id>a.id)
INSERT @TB
SELECT 1 , 6 , 'aaa' UNION ALL
SELECT 2 , 7 , 'bbb'UNION ALL
SELECT 3 , 6 , 'aaa'UNION ALL
SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND ID>T.ID)SELECT * FROM @TB(所影响的行数为 4 行)
(所影响的行数为 1 行)id uid email
----------- ----------- ----------
2 7 bbb
3 6 aaa
4 8 ddd(所影响的行数为 3 行)
INSERT @TB
SELECT 1 , 6 , 'aaa' UNION ALL
SELECT 2 , 7 , 'bbb'UNION ALL
SELECT 3 , 6 , 'aaa'UNION ALL
SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND ID<T.ID)SELECT * FROM @TB
(所影响的行数为 4 行)
(所影响的行数为 1 行)id uid email
----------- ----------- ----------
1 6 aaa
2 7 bbb
4 8 ddd(所影响的行数为 3 行)
create table test(id int,uid int,email varchar(20))
insert test
select 1,6,'aaa' union all
select 2,7,'bbb' union all
select 3,6,'aaa' union all
select 4,8,'ddd' godelete t from test t where
exists(select * from test where t.uid = uid and t.email = email and id>t.id)select * from test
(所影响的行数为 1 行)id uid email
----------- ----------- --------------------
2 7 bbb
3 6 aaa
4 8 ddd(所影响的行数为 3 行)
INSERT @TB
SELECT 1 , 6 , 'aaa' UNION ALL
SELECT 2 , 7 , 'bbb'UNION ALL
SELECT 3 , 6 , 'aaa'UNION ALL
SELECT 4 , 8 , 'ddd'DELETE @TB FROM @TB T WHERE EXISTS(SELECT 1 FROM @TB WHERE EMAIL=T.EMAIL AND UID=T.UID AND ID<T.ID)SELECT * FROM @TB(所影响的行数为 4 行)
(所影响的行数为 1 行)id uid email
----------- ----------- ----------
1 6 aaa
2 7 bbb
4 8 ddd(所影响的行数为 3 行)
declare @tb table(id int, uid int, email varchar(3))
insert @tb select
1, 6 , 'aaa' union all select
2 , 7 , 'bbb' union all select
3 , 6 , 'aaa' union all select
4 ,8, 'ddd'delete @tb
from @tb t
where exists(select * from @tb where uid=t.uid and email=t.email and id<t.id)
select * from @tb
id uid email
----------- ----------- -----
1 6 aaa
2 7 bbb
4 8 ddd(3 行受影响)