两道SQL面试题

        最近面试的两道SQL题目，自认为行走江湖这么多年，这些题目总难不倒我吧！结果却相差巨大，都没做对。愧对大家一直以来对我的期望。我要反思自己的不足之处，虚心听取建议，认真的学习。这些答案，都是在CSDN上面发帖后，论坛里的高手给的答案。真是感激不尽。谢谢！

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 UNION
SELECT 5,'王五','10','5013','数学',95 
GO
--第一种方法：TravyLee
DELETE #T WHERE EXISTS
(SELECT 1 FROM #T B 
WHERE B.NAME=#T.NAME AND B.CLASS=#T.CLASS AND B.StuNo =#T.StuNo AND B.[Subject]=#T.[Subject]  AND B.ID<#T.ID
)
GO
--第二种方法：TravyLee
;with t as
(
select RINDEX=row_number() over(partition by [NAME] order by [StuNo]),* FROM #T
)
DELETE #T WHERE ID IN( SELECT ID FROM t where RINDEX>1)
GO
--第三种方法：ni_sheng
DELETE FROM #T WHERE ID not IN (SELECT max(ID) id FROM #T GROUP BY name,CLASS,StuNo,Subject,Score)
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 A.NAME,B.NAME FROM #B A,#B B WHERE A.NAME < B.NAME ORDER BY a.NAME
GO
DROP TABLE #B