- 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 行受影响)*/
------解决方案--------------------