sql 2005 行数比较
本帖最后由 cqcs0824 于 2012-11-08 11:13:50 编辑 求：查询选修了全部课程的学生信息
我是这样想的 先用 select count(*) from b 求出课程总数 然后 用select count(*)
from c group by sno 求出每个学生选修的课程数 通过比较得到满足条件的sno再得到学生信息 可是应该怎么比较行数 求大大们帮帮忙~
表1学生信息 a
sno sname sexbirthdayNation
08010101 张三 男1992-05-02汉
08050412 李孟才 男1991-08-09藏
08060945 王珊珊 女1993-08-29汉
08110207 杨彤 女1994-05-30汉
表2 课程信息 b
cnocname credit
080601C语言 3
080602数据结构4
080603数据库原理4
080604操作系统4
080605编译原理3.5
表3 成绩信息 c
sno cno score
0801010108060180
0801010108060279
0801010108060365
0806094508060198
0806094508060489
0805041208060185
0805041208060286
0811020708060265
0811020708060554
[最优解释]
select * from a where sno in(select sno from c group by sno
having count(*)=(select count(*) from b))
[其他解释]
select a.sno,a.sname
from 表3 c,表1 a
where a.sno=c.sno
group by a.sno,a.sname
having count(*)=(select count(*) from 表2)
select *
from A
where sno in (
select sno
from (
select sno,count(*) as num,(select count(*) from b) as cnum
from c
group by sno) as x
where num<>cnum)
select a.sno,a.sname
from 表3 c,表1 a
where a.sno=c.sno
group by a.sno,a.sname
having count(*)=(select count(*) 表2 from )
