mysql - 在子查询中访问父列信息

Question

我正在尝试编写一个子查询以包含在更大的查询中，我正在编写的子查询需要从主列中获取 a.workorder_id

这个有效(注意 x.workorder_id = '805' )

SELECT a.* , 
( SELECT SUM( maxes ) qty 
  FROM  ( 
     SELECT MAX( qty ) maxes 
     FROM `rework_line` x 
     WHERE x.workorder_id = '805' 
     GROUP BY x.size 
  ) as tba 
) tmpsum 
FROM rework_line a 
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id

然而，当我更改 x.workorder_id = a.workorder_id 时，它不再起作用并告诉我

#1054 - “where 子句”中的未知列“a.workorder_id”

SELECT a.* , 
( SELECT SUM( maxes ) 
  qty FROM  ( 
     SELECT MAX( qty ) maxes 
     FROM `mtborah_rework_line` x 
     WHERE x.workorder_id = a.workorder_id 
     GROUP BY x.size 
  ) as tba 
) tmpsum 
FROM mtborah_rework_line a 
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id

我确定这是我忘记的简单事情，但是在我的所有搜索中我都找不到该怎么做，我还尝试在 freenode 上的 mysql 室中询问，他们只会说将其重写为连接，这由于我的查询，这是不可能的如果我重新加入该表，它将通过我的计数和总和

Answer 1

也许我遗漏了一些东西，但是为什么不使用连接中的子查询而不是相关子查询来重写它:

SELECT a.*, qty
FROM mtborah_rework_line a 
LEFT JOIN
(
  SELECT SUM(maxes) qty, workorder_id
  FROM
  (
    SELECT MAX( qty ) maxes, x.workorder_id
    FROM `mtborah_rework_line` x 
    GROUP BY x.size, x.workorder_id
  ) m
  GROUP BY workorder_id
) q
  on a.workorder_id = q.workorder_id
WHERE a.workorder_id = '805';

甚至这样:

SELECT a.*, SUM(maxes) qty
FROM mtborah_rework_line a 
LEFT JOIN
(
  SELECT MAX(qty) maxes, x.workorder_id
  FROM `mtborah_rework_line` x 
  GROUP BY x.size, x.workorder_id
) q
  on a.workorder_id = q.workorder_id
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id;