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

qwz_qu521

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

insert into @t
select'xw','male',18 union all
select'mc','女',18 union all
select'mc','女',18 union all
select'mc','女',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
in
(
select b. Student ID
from @t b
inner join
(
select name, gender, age
from @t
group by name, gender, age
having count(1)>1
) a
on a. name=b. name and a. gender=b. gender and a. age=b. age
)

select * from @t
--------------------
What you asked is wrong. Here it is. The NET section is much worse than the SQL section after all

lendoe

What did you write
delete A from Table A
inner jion Table B
on A. Student ID> B. Student ID and A. Name = B. Name and A. Gender = B. Gender and A. Age = B. Age

lakers58

create table students
(
sid varchar(20) primary key,
sname varchar(20),
sex varchar(2),
age int

)

insert into students
values('0001','cc','male',19)
insert into students
values('0002','a','female',11)
insert into students
values('0003','b','male',13)
insert into students
values('0004','cc','male',19)

delete from students where sid =
(
select s1.sid from students s1,students s2 where s1.sname=s2.sname and s1.sid>s2.sid
)

ronin0424

[Quote=Quote the reply ofjsj_yyon the 11th floor:]
Basically.
[/Quote]
in change exists better

ronin0424

The quote was wrong just now.
[Quote=quote the reply ofkcactuson the 12th floor:]
DELETE FROM Test2
where [student number] in
(select [study number]=min([study number]) from Test2 group by [name],[gender],[age] having count(*)> 1)
[/Quote]
in change exists better