求一sql语句，删除重复记录,该如何处理

求一sql语句，删除重复记录

SQL code

CREATE TABLE STU(    stu_number varchar(20),    stu_name varchar(20),    stu_age int)INSERT INTO STU SELECT '110','张三',10 UNION SELECT '110','张三',10 UNION SELECT '111','李四',11 UNION SELECT '111','李四',11 UNION SELECT '112','王五',10

SQL code

CREATE TABLE STU(    stu_number varchar(20),    stu_name varchar(20),    stu_age int)INSERT INTO STU SELECT '110','张三',10 UNION allSELECT '110','张三',10 UNION all SELECT '111','李四',11 UNION all SELECT '111','李四',11 UNION allSELECT '112','王五',10select * from STUstu_number           stu_name             stu_age-------------------- -------------------- -----------110                  张三                   10110                  张三                   10111                  李四                   11111                  李四                   11112                  王五                   10delete STU from (select row_number() over(partition by stu_number,stu_name,stu_age order by (select 0)) rn, * from STU) STUwhere rn>1select * from STUstu_number           stu_name             stu_age-------------------- -------------------- -----------110                  张三                   10111                  李四                   11112                  王五                   10
[解决办法]
SQL code
CREATE TABLE STU(    stu_number nvarchar(20),    stu_name nvarchar(20),    stu_age int)INSERT INTO STU SELECT '110',N'张三',10 UNION allSELECT '110',N'张三',10 UNION allSELECT '111',N'李四',11 UNION allSELECT '111',N'李四',11 UNION allSELECT '112',N'王五',10goalter table  STU add ID int identitygoDELETE  STU where ID not in(select min(ID) from  STU group by stu_number,stu_name,stu_age)goalter table  STU drop COLUMN IDGOSELECT * FROM STU/*stu_number    stu_name    stu_age110    张三    10111    李四    11112    王五    10*/DROP TABLE STU
[解决办法]
SQL code
CREATE TABLE studentage(    tid int identity(1,1) not null,    primary key(tid),    stu_number nvarchar(20),    stu_name nvarchar(20),    stu_age int)--select * from studentage--drop table studentage--delete studentageINSERT INTO studentage values('110',N'张三',10 )INSERT INTO studentage values('110',N'张三',10 )INSERT INTO studentage values('111',N'李四',11 )INSERT INTO studentage values('111',N'李四',11 )INSERT INTO studentage values('112',N'王五',10 )/*select stu_number as 学生编号,stu_name as 学生姓名,stu_age as 学生年龄   from studentage group by stu_number,stu_name,stu_age*/delete studentage where tid not in(select max(tid) from studentage group by stu_number,stu_name,stu_age)select * from studentage
[解决办法]
SQL code
IF OBJECT_ID('STU','U') IS NOT NULL   DROP TABLE STUGOCREATE TABLE STU(    stu_number varchar(20),    stu_name varchar(20),    stu_age int)INSERT INTO STU SELECT '110','张三',10 UNION ALLSELECT '110','张三',10 UNION ALLSELECT '111','李四',11 UNION ALLSELECT '111','李四',11 UNION ALLSELECT '112','王五',10--方法一使用临时表select distinct stu_number,stu_name,stu_age into #t from STUdelete stuinsert into stu select * from #tselect * from stu--方法二delete a from (select *,row=row_number() over(partition by stu_number,stu_name,stu_age order by getdate()) from stu) a where row>1/*stu_number           stu_name             stu_age-------------------- -------------------- -----------110                  张三                   10111                  李四                   11112                  王五                   10(3 行受影响)*/
------解决方案-------------------- 
 
lz的表结构不合理,每条数据得有一个区别于其他数据的id,这样以后维护起来比较方便
比如像你今天这种情况,要废很大的劲才行.
如果你在建表的当初加入一个自增的id主键,只需要轻松的一句话就能删除重复数据.
SQL code
if object_id('STU') is not nulldrop table STUgoCREATE TABLE STU(    stu_id int identity(1,1) primary key,    stu_number varchar(20),    stu_name varchar(20),    stu_age int)INSERT STU SELECT '110','张三',10 UNION all SELECT '110','张三',10 UNION allSELECT '111','李四',11 UNION allSELECT '111','李四',11 UNION allSELECT '112','王五',10go--筛选重复数据select * from STU as a where  exists (select * from STU where a.stu_number=stu_number and a.stu_id>stu_id)--删除重复数据delete a  from STU as a where  exists (select * from STU where a.stu_number=stu_number and a.stu_id>stu_id)--select * from STUgodrop table stu