sql - 迷你数据库 - 子查询返回超过 1 个值

Question

SELECT DISTINCT L.LecturerName
FROM Lecturer L
WHERE 5 > = 
(   SELECT  E.StudentID
    FROM    Class C, Enrolled E
    WHERE   C.ClassName = E.ClassName
    AND     C.LecturerID = L.LecturerID)

错误信息如下:-

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

有什么问题吗？

Answer 1

您需要在子查询中使用 COUNT(*)。此外，永远不要在FROM 子句中使用逗号。 始终使用正确、明确的 JOIN 语法。

SELECT DISTINCT L.LecturerName
FROM Lecturer L
WHERE 5 >= (SELECT COUNT(*)
            FROM Class C JOIN
                 Enrolled E
                 ON C.ClassName = E.ClassName
            WHERE C.LecturerID = L.LecturerID
           );

我猜测 SELECT 中不需要 DISTINCT。

您没有指定正在使用的数据库。更典型的是，您会将此信息放在 FROM 或 SELECT 子句中，这样您就可以看到学生人数:

SELECT L.LecturerName, COUNT(*) as NumStudents
FROM Lecturer L JOIN
     Class C
     ON C.LecturerID = L.LecturerID JOIN
     Enrolled E
     ON C.ClassName = E.ClassName
GROUP BY L.LecturerName
HAVING COUNT(*) <= 5;