按要求删除重复数据

[AthlonXP2000]

一个表如下

表A：
name sj

abc 2010-2-2
abc 2011-3-3
ddd 2022-1-1


图上name是字符串类型，sj是datetime类型

如何找出时间最早的name数据，并形成一个新表

也就是得到希望得到如下表：
name sj

abc 2010-2-2
ddd 2022-1-1

[nzperfect]

--> 测试数据: #表A
if object_id('tempdb.dbo.#表A') is not null drop table #表A
create table #表A (name varchar(3),sj datetime)
insert into #表A
select 'abc','2010-2-2' union all
select 'abc','2011-3-3' union all
select 'ddd','2022-1-1'

select name,MIN(sj) as sj from #表A
group by name
/*
abc	2010-02-02 00:00:00.000
ddd	2022-01-01 00:00:00.000
*/

[feixianxxx]

select *
from tb k
where not exists(select * from tb where k.name=name and k.sj<sj)

好久不写 来个

[nianran520]

select * from [tb] t
where [sj]=(select min([sj]) from [tb] where [name]=t.[name])

[Mr_Nice]

create Table NewTable(name varchar(10),SJ Smalldatetime)
go

insert into NewTable(name,SJ)
select name,MIN(sj) as sj from #表A
group by name

[nianran520]

select * from [tb] t
where not exists (select 1 from [tb] where [name]=t.[name] and [sj]<t.[sj])

[zhengduan964532]

IF OBJECT_ID('A')IS NOT NULL DROP TABLE A
CREATE TABLE A(NAME VARCHAR(10),SJ DATETIME)
INSERT A
SELECT 'abc' , '2010-2-2' UNION ALL
SELECT 'abc' , '2011-3-3' UNION ALL
SELECT 'ddd' , '2022-1-1' 



DELETE FROM A 
WHERE  EXISTS(SELECT * FROM A C WHERE C.NAME=A.NAME AND C.SJ<A.SJ)

[dawugui]

[Quote=引用楼主 athlonxp2000 的回复:]
一个表如下

表A：
name sj

abc 2010-2-2
abc 2011-3-3
ddd 2022-1-1


图上name是字符串类型，sj是datetime类型

如何找出时间最早的name数据，并形成一个新表

也就是得到希望得到如下表：
name sj

abc 2010-2-2
ddd 2022-1-1

[/Quote]

--search

select t.* from a t where sj = (select min(sj) from a where name = t.name) order by t.name

select t.* from a t where not exists (select 1 from a where name = t.name and sj < t.sj) order by t.name

--delete

delete a from a t where sj = (select min(sj) from a where name = t.name) 

delete a from a t where not exists (select 1 from a where name = t.name and sj < t.sj)

[fsnow2005]

select * into new_table
from tb k
where not exists(select * from tb where k.name=name and k.sj<sj)

[ai_li7758521]

--<一>：
select distinct * into #temp from 表
truncate table 表
insert 表 select * from #temp
drop table


--<二>
带有标识列
--备份数据
select * into #temp from 表
alter table #temp drop column id

--删除原表数据
truncate table msgtable

--恢复数据并去掉重复数据
insert into 表 select distinct * from #temp

--<三>
delete From aa where a in ( select a From aa group by a having count(a)>1)


--<四>
alter table 表 add newfield int identity(1,1)

delete 表
where newfield not in(select min(newfield) from 表 group by 除newfield外的所有字段)

alter table 表 drop column newfield


--<五>
--添加一个处理的标识字段
alter table 表 add id int identity(1,1)
go
    
--删除重复记录
delete a
from 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.id

where b.id is null
go

--删除处理用的标识字段
alter table 表 drop column id




--参考：
/*
一张表里面以两个字段为唯一字段，当几条记录的这两个字段完全相同时，需要删除重复项，如下表   
  a   b   c   d   
  1   2   3   4   
  1   5   3   5   
  1   2   7   9   
  以a、b为唯一字段，第一条和第三条的a、b完全相同，所以，需要删除第一条记录1   2   3   4   或者第三条记录1   2   7   9   
  即如下结果：   
  a   b   c   d   
  1   2   3   4   
  1   5   3   5   
  或   
  a   b   c   d   
  1   5   3   5   
  1   2   7   9   
    
  请问各位大侠这种sql语句怎么写 */  
    
 
CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255))
INSERT Tb1(id, [a], [b], [c], [d])
SELECT 1, '1','2','3','4'
UNION ALL SELECT 2, '1','5','3','5'
UNION ALL SELECT 3, '1','2','7','9'
UNION ALL SELECT 4, '1','4','7','6'

delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1

drop table tb1

如果要同时删除第一和第三行
即如下结果：
a b c d
1 5 3 5

语句如下：

delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
--在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

[qiqi860819]

create table sj
(
name  varchar(10),
sj varchar(20)
)
go
insert into sj
select 'abc',  '2010-2-2' union all 
select 'abc',  '2011-3-3' union all 
select 'ddd',  '2022-1-1'
go


delete a from sj a where exists(select 1 from sj where Name=a.Name and sj<a.sj)

[shuifengcun]

study ing

[jenny0810]

learning