我有下表：
CREATE TABLE studenttest
(
YEAR INT,DEPT VARCHAR(5),SEM INT,REGNO INT,NAME VARCHAR(20),ENGLISH VARCHAR(2),MATHS VARCHAR(2),PHYSICS VARCHAR(2),CHEMISTRY VARCHAR(2),EG VARCHAR(2),FOC VARCHAR(2),LAB1 VARCHAR(2),LAB2 VARCHAR(2),LAB3 VARCHAR(2)
)
有以下数据：
INSERT INTO studenttest values (2010,'cse',3,1,'saravaanan','a','b','c','d','ra','f','g','h','i'),(2010,2,'raja','e','selvam','i')
我想查询这些数据,以获得所有在任何科目中没有“ra”成绩的学生的结果集.
解决方法
SELECT a.*
FROM studenttest a
LEFT JOIN
(
SELECT regno,name
FROM studenttest
WHERE ENGLISH = 'ra' OR
MATHS = 'ra' OR
PHYSICS = 'ra' OR
CHEMISTRY = 'ra' OR
EG = 'ra' OR
FOC = 'ra' OR
LAB1 = 'ra' OR
LAB2 = 'ra' OR
LAB3 = 'ra'
) b ON a.regno = b.regno
AND a.Name = b.Name -- this line is OPTIONAL
WHERE b.regno IS NULL
