select s# form [tablename] where not c#=c3
select s# from [tablename] where c#=c1 and c#=c2 and c#=c3 and c#=c4 and c#=c5
可以在SELECT后加一个DIS什么的可以去除S#的重复
select s# from [tablename] where c#=c1 and c#=c2 and c#=c3 and c#=c4 and c#=c5
可以在SELECT后加一个DIS什么的可以去除S#的重复
第一个问题
select distinct s# from student where c#<>'c3'
第二个问题
select distinct s# from student
不知道我的理解是否正确
select distinct 学号 from tablename where 学号 not in(
select 学号 from tablename where 课程号=c3)选修了全部课程的学生:
好象不能有一个SQL语句,要用一个循环:
set rs=con.exec("select distinct 学号 from tablename")
while not rs.eof
set rs1=con.exec("select count(*) from tablename where 学号=" & rs.fields(0).value)
if rs1.fields(0).value=5 then
''此学号就是选修了全部课程的学生
endif
rs.movenext
wend
SELECT [学号] FROM TableA WHERE [学号] NOT IN(SELECT [学号] FROM TableA WHERE [课程号]="C3") GROUP BY [学号]选修全部课程的学生:
SELECT [学号] FROM TableA GROUP BY [学号] HAVING COUNT([课程号])=5
select [学号] from tableA where [学号] No In (Select [学号] From tableA where [课程号]="C3" Group by 学号 ) 2、
首先假定同一个学号以及课程不会有两个成绩 ,即
不会有这样的重复纪录
1,C1,88
1,C1,98
可以这样写
SELECT 学号, Count(学号) FROM tblstudent GROUP BY 学号 having Count(学号)=5
因为
SELECT 学号, Count(学号) FROM tblstudent GROUP BY 学号
就表示每一个学号选修多少课程如果上面的假定不成立的话,就要写成
select 学号,count(学号) from (select 学号,课程号 from tblstudent group by 学号,课程号) group by 学号 having count(学号)=5
这句SQL语句在Access97 或者VB中使用Access97的数据连接不能通过,因为Access 中from 后面 不能带查询。但是用Access2000可以通过。SQL server没有试过另:在Access97 的类似 Like "*1*" 的查询的结果是错误的,可害了我一把。
SELECT AAA.S, AAA.C, *
FROM AAA
WHERE (((AAA.C)<>"C3"))
第二是用SQL写不出来的
select * from 表名
select distinct 学号 from table where 学号 not in (select 学号 where 学号='C3')
2
select 学号,count(课程号) from table where group by 学号 having count(课程号)=5
select distinct [学号] from tableA where [学号] No In (Select [学号] From tableA where [课程号]="C3" ) 2 全选
select [学号] _
from tableA
where [学号] In (Select distinct [学号] From tableA where [课程号]="C1" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C2" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C3" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C4" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C5" )
Students(StudentID, Name)
1 1
2 2
3 3
4 4
5 5
Subjects(SubjectID, Name)
C1 C1
C2 C2
C3 C3
C4 C4
C5 C5
Grades(StudentID, SubjectID, Grade)
1 C1 A
1 C2 A
1 C3 A
1 C5 B
2 C1 B
2 C2 C
2 C4 C
3 C2 B
3 C3 C
3 C4 B
4 C3 B
4 C4 D
5 C1 D
5 C2 D
5 C3 D
5 C4 D
5 C5 DAnswer to questoin one SELECT Students.*
FROM Students
WHERE NOT EXITS(
SELECT *
FROM Grades
WHERE EXITS(
SELECT *
FROM Subjects
WHERE Grades.StudentID = Students.StudentID AND
Subjects.SubjectID = Grades.SubjectID AND
Subjects.SubjectID = 'C3'))Answer to questoin twoSELECT Students.*
FROM Students
WHERE NOT EXISTS(
SELECT *
FROM Subjects
WHERE NOT EXISTS(
SELECT *
FROM Grades
WHERE Subjects.SubjectID = Grades.SubjectID AND
Students.StudentID = Grades.StudentID))
select distinct [学号] _
from tableA
where [学号] In (Select distinct [学号] From tableA where [课程号]="C1" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C2" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C3" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C4" ) _
and [学号] In (Select distinct [学号] From tableA where [课程号]="C5" )
另一种做法:
SELECT DISTINCT a.学号, count(a.课程号) AS 选课数
FROM [select distinctrow 学号,课程号 from tableA group by 学号,课程号]. AS a
GROUP BY a.学号
HAVING count(a.课程号)=5;