SELECT b.* FROM STUDENT b left join (SELECT S_ID FROM EXAM WHERE S_TYPE='Y' ) a on CONVERT(NVARCHAR(5),b.S_ID)+b.S_CLASS = CONVERT(NVARCHAR(5),a.S_ID)+a.S_CLASS where a.S_ID is null
select b.* from (select * from EXAM where S_TYPE='Y') as a left join STUDENT as b on a.S_ID=b.S_ID and a.S_CLASS!=b.S_CLASS where b.S_CLASS is not null
考虑到EXAM可能重复,我觉得需要去重,SELECT T1.* FROM STUDENT T1 LEFT JOIN(SELECT S_ID,S_CLASS,S_TYPE FROM EXAM GROUP BY S_ID,S_CLASS,S_TYPE) T2 ON T1.S_ID=T2.S_ID AND T1.S_CLASS=T2.S_CLASS AND T2.S_TYPE='Y' WHERE T2.S_ID IS NULL
left join (SELECT S_ID FROM EXAM WHERE S_TYPE='Y' ) a
on CONVERT(NVARCHAR(5),b.S_ID)+b.S_CLASS = CONVERT(NVARCHAR(5),a.S_ID)+a.S_CLASS
where a.S_ID is null
select b.* from (select * from EXAM where S_TYPE='Y') as a left join STUDENT as b
on a.S_ID=b.S_ID and a.S_CLASS!=b.S_CLASS where b.S_CLASS is not null
LEFT JOIN(SELECT S_ID,S_CLASS,S_TYPE FROM EXAM GROUP BY S_ID,S_CLASS,S_TYPE) T2
ON T1.S_ID=T2.S_ID AND T1.S_CLASS=T2.S_CLASS AND T2.S_TYPE='Y'
WHERE T2.S_ID IS NULL