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

qwz_qu521

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 学号
in
(
        select b.学号
        from @t b
        inner join
        (
                select 姓名,性别,年龄
                from @t
                group by  姓名,性别,年龄
                having count(1)>1
        ) a
        on a.姓名=b.姓名 and a.性别=b.性别 and a.年龄=b.年龄
)

select * from @t
--------------------
你问的地方不对 这里是。NET版块 毕竟比SQL版块差多了

lendoe

写的啥啊
delete A from Table A
inner jion Table B
        on A.学号 > B.学号 and A.姓名 = B.姓名 and A.性别 = B.性别 and A.年龄 = B.年龄

lakers58

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

)

insert into students
        values('0001','cc','男',19)
insert into students
        values('0002','a','女',11)
insert into students
        values('0003','b','男',13)
insert into students
        values('0004','cc','男',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=引用 11 楼 jsj_yy 的回复:]
基础而已.
[/Quote]
in 改 exists 更好   

ronin0424

刚才引错了。
[Quote=引用 12 楼 kcactus 的回复:]
DELETE   FROM   Test2   
where   [学号]   in
(select   [学号]=min([学号])   from   Test2   group   by   [姓名],[性别],[年龄]   having   count(*)> 1)
[/Quote]
in 改 exists 更好