学生基础数据表未创建学号列的唯一索引或主键，一次导入误操作导致学号和姓名出现重复数据，亟需进行数据治理。如下哪两条SQL可输出全部重复多余的学生信息？ - 墨天轮每日一题

任务中心

2020-07-14

多选题

123人参与 · 45人答对

来自： GuyDeng — 贡献 12 题

学生基础数据表未创建学号列的唯一索引或主键，一次导入误操作导致学号和姓名出现重复数据，亟需进行数据治理。如下哪两条SQL可输出全部重复多余的学生信息？

暂无图片

A

SELECT SNO,STUDENT_NAME FROM (SELECT DENSE_RANK() OVER(ORDER BY SNO) ROW_SNO,SNO,STUDENT_NAME FROM T_STUDENTS) WHERE ROW_SNO>1;

B

SELECT SNO,STUDENT_NAME FROM T_STUDENTS MINUS SELECT -1 SNO,'NULL' STUDENT_NAME FROM DUAL;

C

SELECT SNO,STUDENT_NAME FROM T_STUDENTS WHERE ROWID IN (SELECT ROWID FROM T_STUDENTS MINUS SELECT MAX(ROWID) FROM T_STUDENTS GROUP BY SNO);

D

SELECT SNO,STUDENT_NAME FROM T_STUDENTS WHERE (SNO,ROWID) IN (SELECT SNO,MAX(ROWID) FROM T_STUDENTS GROUP BY SNO);

E

SELECT SNO,STUDENT_NAME FROM (SELECT ROW_NUMBER() OVER(PARTITION BY SNO ORDER BY ROWID) ROW_SNO,SNO,STUDENT_NAME FROM T_STUDENTS) WHERE ROW_SNO>1;

A. SELECT SNO,STUDENT_NAME FROM (SELECT DENSE_RANK() OVER(ORDER BY SNO) ROW_SNO,SNO,STUDENT_NAME FROM T_STUDENTS) WHERE ROW_SNO>1;

B. SELECT SNO,STUDENT_NAME FROM T_STUDENTS MINUS SELECT -1 SNO,'NULL' STUDENT_NAME FROM DUAL;

C. SELECT SNO,STUDENT_NAME FROM T_STUDENTS WHERE ROWID IN (SELECT ROWID FROM T_STUDENTS MINUS SELECT MAX(ROWID) FROM T_STUDENTS GROUP BY SNO);

D. SELECT SNO,STUDENT_NAME FROM T_STUDENTS WHERE (SNO,ROWID) IN (SELECT SNO,MAX(ROWID) FROM T_STUDENTS GROUP BY SNO);

E. SELECT SNO,STUDENT_NAME FROM (SELECT ROW_NUMBER() OVER(PARTITION BY SNO ORDER BY ROWID) ROW_SNO,SNO,STUDENT_NAME FROM T_STUDENTS) WHERE ROW_SNO>1;

来自： GuyDeng — 贡献 12 题

提交答案

提交答案

讨论

暂无图片

答题后参与讨论

我也要出题，获取更多墨值

扫一扫，小程序答题更便捷

微信订阅二维码

微信订阅，每日一题不错过

题目正确率排行

倒序正序