学号(自动编号) 姓名 性别 年龄0001 xw 男 180002 mc 女 160003 ww 男 210004 xw 男 18请写出实现如下功能的SQL语句:
删除除了学号(自动编号)字段以外,其它字段都相同的冗余记录!
删除除了学号(自动编号)字段以外,其它字段都相同的冗余记录!
(select max(学号(自动编号)) as xh , 姓名 , 性别 , 年龄
from table
group by 姓名 , 性别 , 年龄) A
方法二、
delete table where 学号(自动编号) not in (
select max(a.学号) from table B,(select distinct 姓名 , 性别 , 年龄 from table ) A
where A.姓名=b.姓名 and a.性别=b.性别 and a.年龄 = b.年龄
) C以上方法不知道是否可以,没有在SQL中测试。
DELETE FROM table1
WHERE (学号 NOT IN
(SELECT MAX(学号) AS xh
FROM TABLE1
GROUP BY 姓名, 性别, 年龄))
------------------------
http://fenglin.xland.cn
------------------------
where [学号] in
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄] having count(*)>1)
(学号 int)
insert into #temp
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄]
delete from Test2 where 学号 not in (select 学号 from #temp)
drop #temp
(学号 int)
insert into #temp
(select [学号]=min([学号]) from Test2 group by [姓名],[性别],[年龄])
delete from Test2 where 学号 not in (select 学号 from #temp)
drop table #temp
更正
declare @t table(学号 int identity(1,1),
姓名 varchar(30),性别 nvarchar(1),年龄 int)insert into @tselect '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','男',18select * from @tdelete from @t where 学号 not in (select min(学号) from @t group by 姓名,性别,年龄)select * from @t
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)
)
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 [姓名],[性别],[年龄])
姓名 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','男',18select * from @tdelete 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版块差多了
delete A from Table A
inner jion Table B
on A.学号 > B.学号 and A.姓名 = B.姓名 and A.性别 = B.性别 and A.年龄 = B.年龄
(
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
)
in 改 exists 更好
in 改 exists 更好