mysql - 'headings.heading' 中的未知列 'field list'

Question

执行以下查询时出现以下错误:

#1054 - Unknown column 'headings.heading' in 'field list'

标题表中肯定有一个名为“标题”的列。当我测试它时，问题似乎出在查询的前 5 行中对 headings.heading 的 2 个引用。当我运行它们时，子查询单独工作 - 它们还引用 headings.heading - 我想在子查询之外的某个地方需要引用 FROM headings 吗？我不知道。有什么建议吗？

这是整个查询:

SELECT subquery.first_letter
     , subquery.3rd_column
     , headings.heading 
  FROM ( SELECT letters.first_letter
              , COUNT(LEFT(headings.heading,1)) AS 3rd_column
           FROM ( SELECT 'A' AS first_letter
                  UNION ALL SELECT 'B'
                  UNION ALL SELECT 'C'
                  UNION ALL SELECT 'D'
                  UNION ALL SELECT 'E'
                  UNION ALL SELECT 'F'
                  UNION ALL SELECT 'G'
                  UNION ALL SELECT 'H'
                  UNION ALL SELECT 'I'
                  UNION ALL SELECT 'J'
                  UNION ALL SELECT 'K'
                  UNION ALL SELECT 'L'
                  UNION ALL SELECT 'M'
                  UNION ALL SELECT 'N'
                  UNION ALL SELECT 'O'
                  UNION ALL SELECT 'P'
                  UNION ALL SELECT 'Q'
                  UNION ALL SELECT 'R'
                  UNION ALL SELECT 'S'
                  UNION ALL SELECT 'T'
                  UNION ALL SELECT 'U'
                  UNION ALL SELECT 'V'
                  UNION ALL SELECT 'W'
                  UNION ALL SELECT 'X'
                  UNION ALL SELECT 'Y'
                  UNION ALL SELECT 'Z' ) AS letters
         LEFT OUTER
           JOIN ( SELECT headings.heading
                    FROM town_name
                  INNER
                    JOIN headings
                      ON headings.heading = town_name.heading
                   WHERE town_name.category = 'Classified' ) AS h
             ON LEFT(h.heading,1) = letters.first_letter
         GROUP
             BY letters.first_letter ) AS subquery 
LEFT OUTER
  JOIN ( SELECT headings.heading
           FROM town_name
         INNER
           JOIN headings
             ON headings.heading = town_name.heading
          WHERE town_name.category = 'Classified' ) AS h
    ON LEFT(h.heading,1) = subquery.first_letter      
ORDER
    BY subquery.first_letter 
     , headings.heading

Answer 1

表名或别名headings对您的最外层查询不可用，因为它仅在子查询内部使用。你的决赛JOIN子查询使用 headings在内部选择 heading列，但最终将子查询别名为 h .所以，h.heading应该可用于最外层的查询。在 SELECT 中使用它列表以及 ORDER BY .

SELECT subquery.first_letter
     , subquery.3rd_column
     -- Here in the outer SELECT...
     , h.heading 
  FROM (
  -- snip...
  -- snip...
  -- snip...
LEFT OUTER
  JOIN ( SELECT headings.heading
           FROM town_name
         INNER
           JOIN headings
             ON headings.heading = town_name.heading
          -- Aliased here as `h` and providing `heading`
          WHERE town_name.category = 'Classified' ) AS h
    ON LEFT(h.heading,1) = subquery.first_letter      
ORDER
    BY subquery.first_letter 
     -- And here....
     , h.heading