如何在SQL语句中表示关系代数的除法操作?

kevin_ye

如学生选课表stud_course(stud_id, course_id,grade)和课程表(course_id,course_name).
请问: 如何在学生选课表中找出选修所有课程的学生ID?
这个好像是关系代数所说的除法操作,但我不知道怎么写.
请大侠指点，先谢了!

以下是源代码:

1. create table stud_course
   
2. (
   
3.   stud_id   number,
   
4.   course_id number,
   
5.   grade     char(1)
   
6. );
   
7. 
   
8. insert into stud_course values(1001,2001,'A');
   
9. insert into stud_course values(1001,2002,'B');
   
10. insert into stud_course values(1001,2003,'C');
    
11. insert into stud_course values(1002,2001,'B');
    
12. insert into stud_course values(1003,2001,'A');
    
13. insert into stud_course values(1003,2002,'B');
    
14. commit;
    
15. 
    
16.   
    
17. create table course
    
18. (
    
19.   course_id   number,
    
20.   course_name varchar2(50)
    
21. );
    
22. 
    
23. insert into course values(2001,'English');
    
24. insert into course values(2002,'Math');
    
25. insert into course values(2003,'Politics');
    
26. commit;

复制代码

[ 本帖最后由 kevin_ye 于 2008-1-11 10:48 编辑 ]

dino623

select sid from (
select count(*) cnt, stud_id sid from stud_course group by stud_id)
where cnt = (select count(*) from course)

某些情況下（例如stud_course表里有重複項）這個語句就不成立了

ilove0kyo

我记得  选修了所有的课程就意味着没有一门他没选

首先 他没选的课
SELECT *ＦＲＯＭ　table_course   A WHERE NOT EXISTS (SELECT * FROM stu_course B where a.cource_id=b.course);
其次 再次否定  这样的学生不再这里。

select * from stu_course  C not exists (
SELECT *ＦＲＯＭ　table_course   A WHERE NOT EXISTS (SELECT * FROM stu_course B where a.cource_id=b.course and c.stud_id=a.stud_id);

dino623

select distinct(stud_id) from stud_course where stud_id not in(
select stud_id from(
select a.stud_id,b.course_id from
(select distinct(stud_id) from stud_course) a,
(select course_id from course) b
MINUS
select stud_id ,course_id from stud_course)
)


按樓上的思路做出來的語句

kevin_ye

非常感谢dino623 和ilove0kyo.
我整理了一下ilove0kyo的sql,可以运行
------------------------------------------------------
select * from stud_course sc1
where not exists
(
  SELECT * from course c
  WHERE NOT EXISTS
  (
     SELECT * FROM stud_course sc2
     where c.course_id=sc2.course_id
       and sc1.stud_id=sc2.stud_id
   )
);

kevin_ye

谢谢dino623!
你的关系数据库理论很厉害,
完全使用了除法的步骤，用到笛卡尔、差、选择、投影等知识。
-------------------------------------------------------------------
select distinct(stud_id) from stud_course
where stud_id not in
(
  select stud_id from
  (
    select a.stud_id,b.course_id from
    (select distinct(stud_id) from stud_course) a,
    (select course_id from course) b
    MINUS
    select stud_id ,course_id from stud_course
  )
);

bell6248

没那么复杂， 如下：

SQL> select  STUD_ID,
  2          COURSE_ID,
  3          GRADE
  4  from
  5  (select STUD_ID,
  6          COURSE_ID,
  7          GRADE,
  8          count(COURSE_ID) over(partition by STUD_ID) cnt
  9  from stud_course)
10  where cnt = (select count(COURSE_ID) from course);

   STUD_ID  COURSE_ID GRADE
---------- ---------- -----
      1001       2001 A
      1001       2002 B
      1001       2003 C

SQL>

dino623

还是ilove0kyo的方法结构更工整，而且效率更高

kevin_ye

谢谢bell6248.
您是以count来判断,也是一种好方法.
在数据完整性规范的情况下是没错的,
但可能有一个问题:
如果(stud_id, course_id,grade)是学生成绩表的话,
且该学生可以补考,即同一course_id考多次的话,
就要用count(distinct COURSE_ID) over(partition by STUD_ID)

kevin_ye

不一定,
个人认为not exist最终还是要转换为你那种写法,只是简化了一下
-----------------------------------------------------------------------------
SQL> set timing on
SQL>
SQL> select * from stud_course sc1
  2  where not exists
  3  (
  4    SELECT * from course c
  5    WHERE NOT EXISTS
  6    (
  7       SELECT * FROM stud_course sc2
  8       where c.course_id=sc2.course_id
  9         and sc1.stud_id=sc2.stud_id
10     )
11  );

   STUD_ID  COURSE_ID GRADE
---------- ---------- -----
      1001       2001 A
      1001       2002 B
      1001       2003 C

Executed in 0.046 seconds

SQL>
SQL> select * from stud_course
  2  where stud_id not in
  3  (
  4    select stud_id from
  5    (
  6      select a.stud_id,b.course_id from
  7      (select distinct(stud_id) from stud_course) a,
  8      (select course_id from course) b
  9      MINUS
10      select stud_id ,course_id from stud_course
11    )
12  );

   STUD_ID  COURSE_ID GRADE
---------- ---------- -----
      1001       2001 A
      1001       2002 B
      1001       2003 C

Executed in 0.032 seconds

[ 本帖最后由 kevin_ye 于 2008-1-11 13:38 编辑 ]