SQL堂上作业八（子查询的扩展）

删除重复记录

我们可以用一些有意思的子句来执行去重操作。

这是原先的表：

SNO        SNAME
---------- ----------------------------------------
20101      张盼
20102      李伟
20103      邱同
20105      xfz
20105      alpha

我们希望用一些命令进行去重。

方法一

我们可以用ROWID来进行

DELETE FROM student
WHERE ROWID NOT IN
(SELECT MIN(ROWID) 
FROM student 
GROUP BY sno
);

这里的ROWID，个人理解是：每生成一行，SQL就会自动给行加一个行的ID，我们可以直接读取。

在SELECT子句中，通过GROUP BY，把所有sno相同的都化为一类，再用min选出一个sno相同的出来，再进行排除法，就可以删去。

输出如下：

已删除 1 行。

方法二

我们可以用传统的HAVING和COUNT实现

DELETE FROM student 
WHERE (sno) IN
(SELECT sno
FROM student
GROUP BY sno
HAVING COUNT(sno) > 1);

这里是对每一组中，相同的sno数量进行计数，如果大于1了，就将两个都删去，和方法一有一定的区别。

输出如下：

已删除2行。

找出选修了所有课程的同学的名字

我们有三个表：学生姓名——学号表，学生学号——课程表，课程表

我们通过一个三重套娃可以求解该问题

select Sname from student 
where Sno IN
(select Sno from SC
group by Sno 
having count(*) = (select count（*）from course ));

个人理解为：用(select count（*）from course )求出有多少门课

然后在SC表中，用学生ID对数据进行分组，将选课数量够的学生取出，构成一个子表。

然后，基于该子表取出的学生ID，在student中查询学生姓名。

输出如下：

SNAME
----------------------------------------
邱同

查找学号为2011001的同学没有选修的科目

SELECT cname FROM course
WHERE cno NOT IN
(SELECT distinct cno FROM sc
WHERE
sno = 2011001);

输出如下：

CNAME
------------------------------------------------------------
语言学
训诂学
计算机概论
数据库

输出最靠前的N个

我们可以用rownum来进行限制

Select rownum, department_id , a
from
(select department_id ,avg(salary) a
from employees
group by department_id
order by avg(salary) desc
)
where rownum <=3;

这里相当于是，先对表进行了排序，然后再选出表的前面若干项

输出如下：

    ROWNUM DEPARTMENT_ID          A
---------- ------------- ----------
         1            90 19333.3333
         2           110      10150
         3            20       9500

输出待遇最低的N个薪酬

select e.last_name, a.min_sal, e.department_id
from 
(select department_id,min(salary) min_sal
from employees group by department_id) a,
employees e
where a.min_sal=e.salary
and a.department_id=e.department_id
;

输出如下：

LAST_NAME                                             MIN_SAL DEPARTMENT_ID
-------------------------------------------------- ---------- -------------
Kochhar                                                 17000            90
De Haan                                                 17000            90
Lorentz                                                  4200            60
Popp                                                     6900            70
Colmenares                                               2500            30
Olson                                                    2100            50
Kumar                                                    6100            80
Whalen                                                   4400            10
Fay                                                      6000            20
Mavris                                                   6500            40
Gietz                                                    8300           110
 
LAST_NAME                                             MIN_SAL DEPARTMENT_ID
-------------------------------------------------- ---------- -------------
Popp                                                     6900           100
 
已选择12行。