Sql去除表中重复数据

[yuanrui]

前段时间一个朋友面试，面试官发了一个Oracle Sql题目给他。他不太会做，让我帮忙做一下。
好吧，题目大致是这样的

下面是我给他的答案，题目2相对来说花费的时间更多一点

--题目1
SELECT CNO,
(SELECT COUNT(*) FROM T_STUDENT_COURSE_SCORE a WHERE scs.CNO = a.CNO and a.SCORE >= 60 AND a.SCORE < 70) AS '60-70',
(SELECT COUNT(*) FROM T_STUDENT_COURSE_SCORE a WHERE scs.CNO = a.CNO and a.SCORE >= 70 AND a.SCORE < 80) AS '70-80',
(SELECT COUNT(*) FROM T_STUDENT_COURSE_SCORE a WHERE scs.CNO = a.CNO and a.SCORE >= 80 AND a.SCORE < 90) AS '80-90',
(SELECT COUNT(*) FROM T_STUDENT_COURSE_SCORE a WHERE scs.CNO = a.CNO and a.SCORE >= 90 AND a.SCORE <= 100) AS '90-100'
FROM T_STUDENT_COURSE_SCORE scs
GROUP BY scs.CNO;

--题目2
DELETE FROM T_STUDENT_SCORE a
WHERE EXISTS (
	SELECT 1 
	FROM (SELECT b.ROWNUM, b.ROWID FROM T_STUDENT_SCORE b
			WHERE EXISTS (
				SELECT 1 FROM T_STUDENT_SCORE c
				WHERE c.SNAME = b.SNAME AND c.SCORE = b.SCORE
				GROUP BY c.SNAME, b.SCORE
				HAVING COUNT(*) > 1
			)
		)
	) cte
	WHERE cte.ROWNUM > 1 AND a.ROWID = cte.ROWID
);
commit

一直觉得题目2的答案解决办法不是最优的，隔天他在网上找了一个另外的版本的Sql答案，看了下相对我的答案来说是要好一点，答案如下

DELETE FROM T_STUDENT_SCORE 
WHERE ROWID NOT IN (
	SELECT MIN(ROWID) 
	FROM T_STUDENT_SCORE 
	GROUP BY NAME,SCORE
);
commit