表去重,比如id name
1 gate
2 bill
3 bill
4 gate
5 little
6 gate
7 bill===============output==============
1 gate
2 bill
5 little我记得以前 有人写过 delete not exists
我始终写不出来了。
1 gate
2 bill
3 bill
4 gate
5 little
6 gate
7 bill===============output==============
1 gate
2 bill
5 little我记得以前 有人写过 delete not exists
我始终写不出来了。
from tabname a
where not exists (
select 1
from tabname
where name = a.name
and id < a.id
)
from tabname a
where exists (
select 1
from tabname
where name = a.name
and id < a.id
)
--1 gate
--2 bill
--3 bill
--4 gate
--5 little
--6 gate
--7 bill--===============output==============
--1 gate
--2 bill
--5 littleif OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int,name varchar(50))
insert into tb
select 1,'gate' union all
select 2,'bill' union all
select 3,'bill' union all
select 4,'gate' union all
select 5,'little' union all
select 6,'gate' union all
select 7,'bill'
select * from tb t where id=(select MIN(id) from tb where name=t.name) order by idid name
----------- --------------------------------------------------
1 gate
2 bill
5 little(3 行受影响)
delete t
from tb t
where exists (select 1 from tb where name = t.name and id > t.id)
--1 gate
--2 bill
--3 bill
--4 gate
--5 little
--6 gate
--7 bill--===============output==============
--1 gate
--2 bill
--5 littleif OBJECT_ID('tb') is not null
drop table tb
go
create table tb (id int,name varchar(50))
insert into tb
select 1,'gate' union all
select 2,'bill' union all
select 3,'bill' union all
select 4,'gate' union all
select 5,'little' union all
select 6,'gate' union all
select 7,'bill'
delete tb from tb t where exists (select id,name from tb where name=t.name and id<t.id)
select * from tb
id name
----------- --------------------------------------------------
1 gate
2 bill
5 little(3 行受影响)
use tempdb;
/*
create table A
(
id int not null,
name nvarchar(10) not null
);
insert into A values
(1,'gate'),
(2,'bill'),
(3,'bill'),
(4,'gate'),
(5,'little'),
(6,'gate'),
(7,'bill');
*/
select B.id,B.name
from
(
select *,row_number() over(partition by A.name order by A.id) as [orderno]
from A
) as B
where B.[orderno] = 1
order by B.id
use tempdb;
/*
create table A
(
id int not null,
name nvarchar(10) not null
);
insert into A values
(1,'gate'),
(2,'bill'),
(3,'bill'),
(4,'gate'),
(5,'little'),
(6,'gate'),
(7,'bill');
*/
delete from A
where A.id in
(
select B.id
from
(
select *,row_number() over(partition by A.name order by A.id) as [orderno]
from A
) as B
where B.[orderno] = 1
--order by B.id
);
use tempdb;
/*
create table A
(
id int not null,
name nvarchar(10) not null
);
insert into A values
(1,'gate'),
(2,'bill'),
(3,'bill'),
(4,'gate'),
(5,'little'),
(6,'gate'),
(7,'bill');
*/
delete from A
where A.id not in
(
select B.id
from
(
select *,row_number() over(partition by A.name order by A.id) as [orderno]
from A
) as B
where B.[orderno] = 1
--order by B.id
);
delete t from table as t
where exists (select 1 from table where name = t.name and id > t.id)
declare @T table (id int,name varchar(6))
insert into @T
select 1,'gate' union all
select 2,'bill' union all
select 3,'bill' union all
select 4,'gate' union all
select 5,'little' union all
select 6,'gate' union all
select 7,'bill'select * from @T t where id=
(select min(id) from @T where name=t.name) order by 1/*
id name
----------- ------
1 gate
2 bill
5 little
*/
declare @T table (id int,name varchar(6))
insert into @T
select 1,'gate' union all
select 2,'bill' union all
select 3,'bill' union all
select 4,'gate' union all
select 5,'little' union all
select 6,'gate' union all
select 7,'bill'
--删除
delete t from @T t where exists
(select top 1 * from @T where name=t.name and id<t.id)
--查询
select * from @T
/*
id name
----------- ------
1 gate
2 bill
5 little
*/
create table t1(id int,name varchar(10))
insert into t1
select 1, 'gate' union all
select 2, 'bill' union all
select 3, 'bill' union all
select 4, 'gate' union all
select 5, 'little' union all
select 6, 'gate' union all
select 7, 'bill'
go
delete a from t1 as a
where exists (select 1 from t1 where a.name=name and a.id>id)
go
select * from t1
go
drop table t1