三个表的 SQL 查询

Question

我必须为我的学校创建一个数据库，现在我有这三个表:

Note                               Examination               Subject
+------+------+------+--------+    +------+----+--------+    +------+----------+
| _idS | _idP | Note | Punkte |    | _idP | idF| Thema  |    | _idF |   Name   |
+------+------+------+--------+    +------+----+--------+    +------+----------+
|    1 |    2 |    4 |     55 |    |    1 |  2 | Test 1 |    |    1 | Englisch |
|    2 |    2 |    2 |     80 |    |    2 |  4 | Test 2 |    |    2 | Deutsch  |
|    3 |    2 |    1 |     95 |    |    3 |  4 | Test 3 |    |    3 | Mathe    |
|    1 |    3 |    1 |     90 |    +------+----+--------+    |    4 | Physik   |
+------+------+------+--------+                              +------+----------+

现在我想全选 Notes来自 Name 给出的特殊主题和他的 ID (_idS)给的特殊学生.我试过这样的事情:

SELECT N._idP, N.Note, N.Punkte, E.Thema FROM Note N, Examination E 
WHERE  N._idS='1' AND N._idP=(
       SELECT E._idP FROM Examination E WHERE E.idF=(
              SELECT S._idF FROM Subject S WHERE S.Name='Physik')
       );

// N._idS=1 is the special pupil
// S.Name='Physik' is the special subject

但这不会导致正确的输出。为了让它更清楚一点:我想要这个输出:

+--------+--------+----------+---------+
| N._idP | N.Note | N.Punkte | E.Thema |
+--------+--------+----------+---------+
|      2 |      4 |       55 | Test 2  |
|      3 |      1 |       90 | Test 3  |
+--------+--------+----------+---------+

我的 SQL 语句有什么问题，我必须更改什么才能获得上述结果？
所有带下划线的列名 _开头是 PRIMARY KEYS .所有其他的都是 FOREIGN KEYS .

Answer 1

您的查询有什么问题首先是没有条件关联外部 Note和 Examination表。

此外，事实是没有JOIN由于您使用的是旧式非 ANSI 连接语法，这两个表之间被掩盖了——其中列出的表以逗号分隔，连接条件在 WHERE 中。条款。我建议你用简单的 JOINs 来解决这个问题。没有 IN子句，此外，您使用现代 ANSI 语法:

SELECT
   N._idP,
   N.Note,
   N.Punkte,
   E.Thema
FROM
   Note AS N
   INNER JOIN Examination AS E
      ON N._idP = E._idP
   INNER JOIN Subject AS S
      On E.idF = S._idF
WHERE
   N._idS = 1
   AND S.Name = 'Physik'
;

See a Live Demo at SQL Fiddle (SQL Server 2008 版)

我很抱歉没有提供 SQLite 示例——我没有安装 Web SQLite，而且 javascript SQLite 版本的加载速度非常非常慢。

而 IN (subquery or correlated subquery)语法在一方面更容易理解，根据我的经验，它的使用往往会在概念上影响人，并妨碍普通的基本 JOIN思考。我鼓励您使用 JOIN s 并默认使用 IN (subquery)尽可能。如果你能做到这一点，你就能理解 IN查询也是如此。但它似乎并没有反过来工作。

使用 ANSI JOIN 的一大好处语法是，如果您没有 ON子句，查询将不会运行。此外，它还在 WHERE 中将条件与需要它们的表放在一起，而不是远离它们。子句，此外，它还在逻辑上将被视为基本 JOIN 的一部分的部分分开。 (这些表将始终使用)以及被视为特殊的部分 WHERE此特定查询的过滤器。