第一题

--有如下表，删除数据表中重复记录（只保留一条重复记录）
CREATE TABLE #T
(
ID INT PRIMARY KEY,
[NAME] NVARCHAR(50),
[CLASS] NVARCHAR(50),
[StuNo] nvarchar(50),
[Subject] nvarchar(50),
[Score] int
)
GO
INSERT INTO #T 
SELECT 1,'张三','10','5011','数学',95 UNION
SELECT 2,'李四','10','5011','数学',95 UNION
SELECT 3,'张三','10','5011','数学',95 UNION 
SELECT 4,'王五','10','5011','数学',95 
GO
SELECT * FROM #T
GO
DROP TABLE #T

第二题：
--有四支球队（A,C,D,D），让他们，分别组合对打。如A-B;A-C;A-D;B-C;B-D;C-D;
CREATE TABLE #B
(
[NAME] VARCHAR(1)
)
GO
INSERT INTO #B
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D' 
GO
SELECT * FROM #B
GO
DROP TABLE #B

SELECT A.NAME,B.NAME
FROM #BA,#B B
WHERE A.NAME < B.NAME

------解决方案--------------------
SQL code

--有如下表，删除数据表中重复记录（只保留一条重复记录）
CREATE TABLE #T
(
ID INT PRIMARY KEY,
[NAME] NVARCHAR(50),
[CLASS] NVARCHAR(50),
[StuNo] nvarchar(50),
[Subject] nvarchar(50),
[Score] int
)
GO
INSERT INTO #T 
SELECT 1,'张三','10','5011','数学',95 UNION
SELECT 2,'李四','10','5012','数学',95 UNION
SELECT 3,'张三','10','5011','数学',95 UNION 
SELECT 4,'王五','10','5013','数学',95 
GO
delete  from #T 
where exists(select 1 from #T b where b.[StuNo]=#T.[StuNo]  and b.ID>#T.ID)
GO
select * from #t
/*
ID    NAME    CLASS    StuNo    Subject    Score
2    李四    10    5012    数学    95
3    张三    10    5011    数学    95
4    王五    10    5013    数学    95
*/

--第二题：
--有四支球队（A,C,D,D），让他们，分别组合对打。如A-B;A-C;A-D;B-C;B-D;C-D;
CREATE TABLE #B
(
[NAME] VARCHAR(1)
)
GO
INSERT INTO #B
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D' 
GO
select * from #B a
cross join #B b
where a.NAME<b.NAME
/*
NAME    NAME
----------------
A    B
A    C
B    C
A    D
B    D
C    D
*/

------解决方案--------------------
如果是除了ID其他字段值都相同的话，如下
DELETE FROM #T
WHERE EXISTS(SELECT 1 FROM #T T1 WHERE T1.NAME=#T.NAME AND T1.CLASS=#T.CLASS AND
 T1.StuNo=#T.StuNo AND T1.Subject=#T.Subject AND T1.Score=#T.Score AND T1.ID>#T.ID )






------解决方案--------------------
SQL code
--第一题

;with t as
(
select id=row_number() over(partition by [NAME] order by [StuNo]),*
)
delete from t where id>1

------解决方案--------------------
SELECT * FROM #t
DELETE FROM  #T WHERE ID not IN (SELECT max(ID) id FROM #T  GROUP BY name,CLASS,StuNo,Subject,Score)