Sql去除表中重复数据

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

下面是我给他的答案，题目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

yuanrui / blog

Sql去除表中重复数据 #8