mysql - 字段列表中的未知表

我正在尝试构建一个看似相当简单的嵌套查询，但是当我将以下两个工作查询结合在一起时，出现以下错误：

字段列表中的未知表“ cm”
错误编号。 = 1109。

SELECT `cm`.`cans_id`   AS `cans_id`,
       `cm`.`cans_date` AS `cans_date`,
       `cm`.`begintfin` AS `begIntFin`,
       `c`.`cans_id`    AS `CAN_ID`,
       `c`.`q001`       AS `q001`,
       `c`.`q002`       AS `q002`,
       `c`.`q093`       AS `q093`,
       `c`.`q094`       AS `q094`,
       `c`.`mru`        AS `mru2`,
       (SELECT Count(0) AS `count(*)`
        FROM   `cans2notes` `nt`
               JOIN `cans2meta` `cm`
                 ON `cm`.`cans_id` = `nt`.`cans_id`
               JOIN `cans2surveys` `c`
                 ON `c`.`cans_id` = `cm`.`cans_id`
        WHERE  ( `nt`.`cans_id` = `c`.`cans_id` ));

任何想法，将不胜感激。

最佳答案

您正在引用表，但是没有FROM或WHERE子句。

基本上你有

select col,
       col,
       col,
       col,
       (select count(*) from sometable)

但是你没有

 FROM table cm
 INNER JOIN table c
 ON CM.ID = C.OTHER_ID

我猜您正在尝试执行以下操作：

SELECT `cm`.`cans_id`   AS `cans_id`,
       `cm`.`cans_date` AS `cans_date`,
       `cm`.`begintfin` AS `begIntFin`,
       `c`.`cans_id`    AS `CAN_ID`,
       `c`.`q001`       AS `q001`,
       `c`.`q002`       AS `q002`,
       `c`.`q093`       AS `q093`,
       `c`.`q094`       AS `q094`,
       `c`.`mru`        AS `mru2`,
       `count(*)`       AS 'count'
        FROM   `cans2notes` `nt`
               JOIN `cans2meta` `cm`
                 ON `cm`.`cans_id` = `nt`.`cans_id`
               JOIN `cans2surveys` `c`
                 ON `c`.`cans_id` = `cm`.`cans_id`
        WHERE  ( `nt`.`cans_id` = `c`.`cans_id` )
        GROUP BY `cm`.`cans_id`,
       `cm`.`cans_date`,
       `cm`.`begintfin`,
       `c`.`cans_id`,
       `c`.`q001`,
       `c`.`q002`,
       `c`.`q093`,
       `c`.`q094`,
       `c`.`mru`;

关于mysql - 字段列表中的未知表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17245730/