A classic sql interview question! I think I have a good time playing with sql!

fleetadmiral

It still seems wrong. . If there are three, one is deleted.

fleetadmiral

create table #temp
(Student ID int)
insert into #temp
(select [Student ID] = min ([Student ID]) from Test2 group by [Name], [Gender], [Age]
delete from Test2 where student ID not in (select student ID from #temp)
drop #temp

fleetadmiral

create table #temp
(Student ID int)
insert into #temp
(select [Student ID] = min ([Student ID]) from Test2 group by [Name], [Gender], [Age])
delete from Test2 where student ID not in (select student ID from #temp)
drop table #temp
correct

terrorlin

delete from table where exists (select 1 from (select max (student number) as student number from table group by name, gender, age) table alias where table alias. student number! = table. student number)

pronice

declare @t table (Student ID int identity (1,1),
Name varchar (30), gender nvarchar (1), age int)

insert into @t

select 'xw', 'Male', 18 union all
select 'mc', 'Female', 18 union all
select 'mc', 'Female', 18 union all
select 'mc', 'Female', 18 union all
select 'ww', 'Male', 21 union all
select 'xw', 'Male', 18 union all
select 'xw', 'Male', 18

select * from @t

delete from @t where student number not in (select min (student number) from @t group by name, gender, age)

select * from @t

椒盐飞过海

Very basic sql!

lz Don't have to do this to solve my own problem?

113865278

9494 haha

kcactus

DELETE FROM Test2
where [学号] in(
select a.[Student ID]
from Test2 a,(select [name],[sex],[age] from Test2 group by [name],[sex],[age] having count(*)>1)b
where a.[name]=b.[name] and a.[gender]=b.[sex] and a.[age]=b.[age]
and
[Student ID] not in
(select [Student ID]=min([Student ID]) from Test2 group by [Name],[Gender],[Age] having count(*)>1)
)

小学生xxs

Ha ha, did I understand the wrong landlord? This question is not difficult.
create table #table
(
[Student ID] varchar(5),
[Name] varchar(20),
[Gender] varchar(2),
[Age] int
)
--drop table #table
insert into #table ([Student ID],[Name],[Gender],[Age])
select '0001','xw','Male',18 union all
select '0002','mc','Female',18 union all
select '0003','mc','Female',18 union all
select '0004','mc','Female',18 union all
select '0005','ww','Male',21 union all
select '0006','xw','Male',18 union all
select '0007','xw','Male',18

--Show what needs to be kept
select min (student number), [name], [gender], [age] from #table
group by [name], [gender], [age] order by min (student number)

- delete the ones that do not need to be kept
delete --select *
from #table
where [Student ID] not in (select min(Student ID) from #table
group by [name], [gender], [age])

xiao5zizj

It collapsed, and none of them is correct yet.
The subject is to delete all duplicate records, not to keep a record.