mysql - 错误 1054 (42S22) : Unknown column '' in 'where clause'

Question

MySQL 查询出现以下错误。

ERROR 1054 (42S22): Unknown column 'scope.full_name' in 'where clause'

这里有两个表格如下。

1.范围:

 +-----------+----------+-------------+--------------+------+----------+--------------+
    | handle_id | model_id | define_name | full_name    | name | file     | parent_scope |
    +-----------+----------+-------------+--------------+------+----------+--------------+
    |  15859248 |        0 | tb          | tb           | tb   | 12755864 |            0 |
    |  15859360 |        0 | RCA         | tb.RCA       | RCA  | 12755864 |     15859248 |
    |  15859472 |        0 | FA          | tb.RCA.x1    | x1   | 12755864 |     15859360 |
    |  15859584 |        0 | HA          | tb.RCA.x1.x1 | x1   | 12755864 |     15859472 |
    |  15859696 |        0 | HA          | tb.RCA.x1.x2 | x2   | 12755864 |     15859472 |
    |  15859808 |        0 | FA          | tb.RCA.x2    | x2   | 12755864 |     15859360 |
    |  15859920 |        0 | HA          | tb.RCA.x2.x1 | x1   | 12755864 |     15859808 |
    |  15860032 |        0 | HA          | tb.RCA.x2.x2 | x2   | 12755864 |     15859808 |
    |  15860144 |        0 | FA          | tb.RCA.x3    | x3   | 12755864 |     15859360 |
    |  15860256 |        0 | HA          | tb.RCA.x3.x1 | x1   | 12755864 |     15860144 |
    |  15860368 |        0 | HA          | tb.RCA.x3.x2 | x2   | 12755864 |     15860144 |
    |  15860480 |        0 | FA          | tb.RCA.x4    | x4   | 12755864 |     15859360 |
    |  15860592 |        0 | HA          | tb.RCA.x4.x1 | x1   | 12755864 |     15860480 |
    |  15860704 |        0 | HA          | tb.RCA.x4.x2 | x2   | 12755864 |     15860480 |
    +-----------+----------+-------------+--------------+------+----------+--------------+

2.文件:

+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+
| handle_id | model_id | max_time | min_time | name                                                              | scale_unit |
+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+
|  12755864 |        0 |    10000 |        0 | /directory/ART.txt                                               | 1ns        |
+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+

查询命令为:

SELECT `name` FROM `file` WHERE `scope`.`full_name`='tb.RCA';

我理解MySQL中的反引号意味着“在表/列名中允许空格和其他特殊字符(显然反引号除外)”，但即使下面的命令也会导致相同的错误。

 SELECT name FROM file WHERE scope.full_name='tb.RCA';

更多信息，Linux系统上的MySQL服务器版本为5.7.20。

Answer 1

您需要像这样连接表:

SELECT f.name
FROM file f 
JOIN scope s on s.file = f.handle_id
WHERE s.full_name = 'tb.RCA'

行 JOIN range s on s.file = f.id 使用 scope 表中的列 file 连接表数据，并file 表中的 handle_id 列。

我已链接this website对于这样的问题很多次 - 这是对连接的一个很好的视觉解释。