MySQL 查询失败

Question

这个 MySQL 查询失败了，我只是不明白为什么。感谢您的帮助，如果您需要更多信息，请告诉我。

---

新查询和新错误

select * 
from 
(select 
classId as `Class ID`, mjla_db.StudentRecordTable2.studentId as `Student ID`, quizId as `Quiz ID`, quizGrade as `Quiz Grade`, mjla_db.StudentTable2.lastName, mjla_db.StudentTable2.firstName 
from mjla_db.StudentRecordTable2, mjla_db.StudentTable2 
where (mjla_db.StudentRecordTable2.studentId=mjla_db.StudentTable2.studentId)) 
as A 
where classId 
in (select mjla_db.ClassTable2.classId 
from mjla_db.ClassTable2 
where mjla_db.ClassTable2.teacherId='T1');

ERROR 1054 (42S22): Unknown column 'classId' in 'IN/ALL/ANY subquery'

---

select * 
from 
    (select 
        classId as 'Class ID',
        studentId as 'Student ID',
        quizId as 'Quiz ID',
        quizGrade as 'Quiz Grade',
        mjla_db.StudentTable2.lastName,
        mjla_db.StudentTable2.firstName
    from
        mjla_db.StudentRecordTable2,
        mjla_db.StudentTable2
    where
        (mjla_db.StudentRecordTable2.studentId = mjla_db.StudentTable2.studentId)
    ) as A
where
    A.classId in 
        (select
            mjla_db.ClassTable2.classId
        from
            mjla_db.ClassTable2
        where
            mjla_db.ClassTable2.teacherId='T1'
        );

---

describe StudentRecordTable2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classId   | varchar(20) | NO   | MUL | NULL    |       |
| studentId | varchar(20) | NO   | MUL | NULL    |       |
| quizGrade | tinyint(4)  | YES  |     | NULL    |       |
| quizId    | int(11)     | NO   | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

---

describe ClassTable2;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| classId       | varchar(20) | NO   | PRI | NULL    |       |
| className     | varchar(10) | NO   |     | NULL    |       |
| classSection  | varchar(5)  | NO   |     | NULL    |       |
| classSemester | varchar(2)  | NO   |     | NULL    |       |
| classYear     | varchar(4)  | NO   |     | NULL    |       |
| teacherId     | varchar(20) | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

---

describe StudentTable2;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| studentId       | varchar(20) | NO   | PRI | NULL    |       |
| lastName        | varchar(45) | NO   |     | NULL    |       |
| firstName       | varchar(45) | NO   |     | NULL    |       |
| studentPassword | varchar(32) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Answer 1

我认为您需要使用 bactick / 反引号 (`) 字符来引用您的列名称，而不是单引号字符 (')。

此外，您在第一个子查询中将 classId 别名为 Class ID，但随后在外部 where 子句中引用 A.classId。

Answer 2

由于您要从 StudentRecordTable2 和 StudentTable2 中进行选择，因此您不能只执行 SELECT StudentId 因为该列名称在表和 mysql 中都使用不知道你想要哪一个。您需要指定您想要的表的 studentId，方法是使用表名称作为前缀，就像您稍后在查询中所做的那样。例如，mjla_db.StudentTable2.studentId。

Answer 3

select * from (select ...) 是不正确的语法。 select * from 后面必须跟表名称。我认为您只需要删除第一个，然后使用括号内的查询即可。

我不确定您是否可以在与 AS 一起使用的标识符内使用空格。