sql statement问题

stanleywoon

假设:
student A有的科目:subjA, subB,SubC
student B : subjA, subjC
student C: subjB,subjC
student D: subjc

如果只要select所有student没有subjA的(result就是studentC和D).sql要怎么写??谢谢

max5007

SELECT ＊ FROM XXX WHERE SUBJECT NOT LIKE ＝＇SUBJECTA＇

DRAGONISM

原帖由 stanleywoon 于 28-8-2005 01:06 AM 发表
假设:
student A有的科目:subjA, subB,SubC
student B : subjA, subjC
student C: subjB,subjC
student D: subjc

如果只要select所有student没有subjA的(result就是studentC和D).sql要怎么写??谢谢

如果这个table只有一个subj (vchar) 的 field, 用

SELECT ＊ FROM XXX WHERE subj NOT LIKE ＝ '%subjA%'

stanleywoon

原帖由 DRAGONISM 于 28-8-2005 08:45 PM 发表


如果这个table只有一个subj (vchar) 的 field, 用

SELECT ＊ FROM XXX WHERE subj NOT LIKE ＝ '%subjA%'

如果用这方法会select到全部人因为所有人也同时有其他subject

flashang

SELECT * from student WHERE student.student NOT in (SELECT student.student from student WHERE subject = 'subjA')


student    subject
------------------
c          subjB
c          subjC
d          subjD

flashang

当然你也可以

SELECT * from student WHERE student.student NOT in (SELECT student.student from student WHERE subject = 'subjA') GROUP BY student

由于 GROUP BY student, 所以 c 只有一个记录。

student     subject
-------------------
c           subjC
d           subjC

SELECT distinct student from tblstudent WHERE tblstudent.student NOT in (SELECT tblstudent.student from tblstudent WHERE subject = 'subjA')

either

SELECT student from tblstudent WHERE tblstudent.student NOT in (SELECT tblstudent.student from tblstudent WHERE subject = 'subjA')
GROUP BY student


Group by student 的话就只能 SELECT student 而已

Result : -

student     
-------
c           
d