SQL删除重复数据

记录一下！

CREATE TABLE DuplicateTable
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   USERNAME NVARCHAR(20),    
   AGE INT,
   PROVINCE NVARCHAR(20),
   CITY NVARCHAR(20),
   ToTALGrades DECIMAL(18,2)
)
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Yangzhi',23,'四川','成都',600.5);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Yangzhi',23,'四川','汶川',600.5);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Yangzhi',23,'四川','成都',605);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Yangzhi',21,'四川','成都',600.5);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Zhangsan',24,'重庆','重庆',559);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Zhangsan',24,'重庆','重庆',559);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Lisi',22,'重庆','重庆',559);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Lisi',22,'重庆','重庆',559);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Wangwu',22,'重庆','重庆',595);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Wangwu',23,'重庆','重庆',595);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Lisi',23,'重庆','重庆',600.2);
INSERT INTO DuplicateTable(USERNAME,AGE,PROVINCE,CITY,ToTALGrades)
VALUES('Wangwu',23,'重庆','重庆',595);


SELECT * FROM DuplicateTable dt

DECLARE
    @sql NVARCHAR(4000),
    @groupByConditions NVARCHAR(800)
SELECT @groupByConditions=ISNULL(@groupByConditions+',','')+ col.name FROM ( SELECT s.name FROM syscolumns s WHERE id=OBJECT_ID(N'DuplicateTable') AND s.name<>'ID')col
SET @sql='SELECT MIN(ID) ID  FROM DuplicateTable dt GROUP BY '+@groupByConditions+' ORDER BY ID ASC'
SET @sql= 'DELETE * FROM DuplicateTable dt WHERE dt.ID NOT IN('+@sql+')'
EXEC(@sql)



--查询一个数据库有那些数据表
SELECT * FROM sysobjects s WHERE s.xtype='U'

--查询一个表有那些列
SELECT s.name FROM syscolumns s WHERE id=OBJECT_ID(N'DuplicateTable') AND s.name<>'ID'