一道经典的sql面试题!认为自己sql玩得挺转的进来!

fleetadmiral

好像还是不对。。3个的话少删除了一个。

fleetadmiral

create table #temp
(学号 int)
insert into #temp
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄]
delete from Test2 where 学号 not in (select 学号 from #temp)
drop #temp

fleetadmiral

create table #temp
(学号 int)
insert into #temp
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄])
delete from Test2 where 学号 not in (select 学号 from #temp)
drop table #temp
更正

terrorlin

delete from table where exists (select 1 from (select max(学号) as 学号 from table group by 姓名，性别，年龄) 表别名 where 表别名.学号 ！= table.学号)

pronice

declare @t table(学号 int identity(1,1),
姓名 varchar(30),性别 nvarchar(1),年龄 int)

insert into @t

select 'xw','男',18 union all
select 'mc','女',18 union all
select 'mc','女',18 union all
select 'mc','女',18 union all
select 'ww','男',21 union all
select 'xw','男',18 union all
select 'xw','男',18

select * from @t

delete from @t where 学号 not in (select min(学号) from @t group by 姓名,性别,年龄)

select * from @t

椒盐飞过海

很基础的sql！

lz 想求解自己的问题也不用这样吧？

113865278

9494 哈哈

kcactus

DELETE FROM Test2
where [学号] in(
select a.[学号]
from Test2 a,(select [姓名],[性别],[年龄] from Test2 group by [姓名],[性别],[年龄] having count(*)>1)b
where a.[姓名]=b.[姓名] and a.[性别]=b.[性别] and a.[年龄]=b.[年龄]
and
[学号] not in
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄] having count(*)>1)
)

小学生xxs

呵呵，是不是我理解错楼主的意思了？这个问题不难啊。
create table #table
(
[学号] varchar(5),
[姓名] varchar(20),
[性别] varchar(2),
[年龄] int
)
--drop table #table
insert into #table ([学号],[姓名],[性别],[年龄])
select '0001','xw','男',18 union all
select '0002','mc','女',18 union all
select '0003','mc','女',18 union all
select '0004','mc','女',18 union all
select '0005','ww','男',21 union all
select '0006','xw','男',18 union all
select '0007','xw','男',18

--显示需要保留的
select min(学号),[姓名],[性别],[年龄] from #table
group by [姓名],[性别],[年龄] order by  min(学号)

--删除不需要保留的
delete --select *
from #table
where [学号] not in (select min(学号) from #table
group by [姓名],[性别],[年龄])

xiao5zizj

崩溃了，没有一个正确的呢。
题目是要求删除所有重复的记录，一个记录都不保留。