sql - SQL-混合AND和OR

我不是SQL专家。我使用的是sql server 2005，我试图找出如何构造一个查询，以便它能够满足几个需求。我有两张表定义如下：

Classroom
 - ID
 - DepartmentID
 - TeacherName
 - RoomNumber

Student
 - ID
 - Name
 - ClassroomID

我试着建立一个查询，上面写着，“把有30多个学生的X系或Y系的教室给我，把有40多个学生的W系或Z系的教室给我。我对如何在sql中正确地混合ands和or感到困惑。目前，我正在尝试以下操作：

SELECT
  c.RoomNumber,
  c.TeacherName
FROM
  Classroom c
WHERE
  c.[DepartmentID]=5 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
  c.[DepartmentID]=6 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 30) OR
  c.[DepartmentID]=7 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40) OR
  c.[DepartmentID]=8 AND (SELECT COUNT(*) FROM Student s WHERE s.ClassroomID=c.ID > 40)

我做错什么了？谢谢您！

最佳答案

SELECT  c.id
FROM    Classroom c
JOIN    Students s
ON      s.Classroom = c.Id
WHERE   DepartmentID IN ('X', 'Y', 'W', 'Z')
GROUP BY
        c.id, c.DepartmentID
HAVING  COUNT(*) >= CASE WHEN DepartmentID IN ('X', 'Y') THEN 30 ELSE 40 END

关于sql - SQL-混合AND和OR，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2327803/