mysql - 如何在 LEFT OUTER JOIN 中访问外部表

Question

在下面的查询中，我尝试使用左外连接中的第一个表。但是我收到错误。

SELECT
       products.id,
       products_cstm.oem_c,
       products.mfr_part_num,
       products.description,
       products.cost,
       products.assigned_user_id,
       customfields_oo.ans
FROM products
          LEFT OUTER JOIN (SELECT  COUNT( q.id )  AS ans
                                  , pq.product_id
                           FROM   products_quotes pq 
                                     LEFT JOIN quotes q
                                           ON pq.quote_id = q.id
                           WHERE  q.deleted = 0
                           AND    pq.deleted = 0
                           AND    q.stage <> 4
                           AND  (pq.qty_shipped < pq.product_qty)
                           AND  pq.product_id = products.id
                           GROUP BY pq.product_id
                      ) AS customfields_oo
                ON customfields_oo.product_id = products.id
          LEFT JOIN products_cstm
                ON products.id = products_cstm.id_c
WHERE  products.deleted = 0
ORDER  BY ans DESC

当我运行查询时，出现以下错误:

Error Code : 1054
Unknown column 'products.id' in 'where clause'

它不允许在左外连接查询中使用第一个“产品”表。

Answer 1

问题在于 customfields_oo 是派生表而不是相关子查询。因此，您无法从派生表的定义中引用外表。在这种情况下，您无法从 customfields_oo 定义中引用外部 products 表。相反，您必须在派生表定义之外的 On 子句中执行该过滤。

Select products.id,
       products_cstm.oem_c,
       products.mfr_part_num,
       products.description,
       products.cost,
       products.assigned_user_id,
       customfields_oo.ans
FROM products
    Left Join   (
                Select pq1.product_id
                    , Count( q1.id ) As ans
                From products_quotes As pq1 
                    Left Join quotes As q1
                        On pq1.quote_id = q1.id
                Where q1.deleted = 0
                    And pq1.deleted = 0
                    And q1.stage <> 4
                    And pq1.qty_shipped < pq1.product_qty
                Group By pq1.product_id
                ) As customfields_oo
        On customfields_oo.product_id = products.id
    Left Join products_cstm
        On products.id = products_cstm.id_c
Where products.deleted = 0
Order By customfields_oo.ans Desc

现在，您在评论中指出这太慢了，因为可能会在派生表中评估已删除 <> 0 的产品。如果是这种情况，则只需扩展派生表以包含外部 products 表上的过滤器即可。

Select products.id,
       products_cstm.oem_c,
       products.mfr_part_num,
       products.description,
       products.cost,
       products.assigned_user_id,
       customfields_oo.ans
FROM products
    Left Join   (
                Select pq1.product_id
                    , Count( q1.id ) As ans
                From products_quotes As pq1 
                    Join products As p1
                        On p1.products.id = pq1.product_id
                    Left Join quotes As q1
                        On pq1.quote_id = q1.id
                Where q1.deleted = 0
                    And pq1.deleted = 0
                    And q1.stage <> 4
                    And pq1.qty_shipped < pq1.product_qty
                    And p1.deleted = 0
                Group By pq1.product_id
                ) As customfields_oo
        On customfields_oo.product_id = products.id
    Left Join products_cstm
        On products.id = products_cstm.id_c
Where products.deleted = 0
Order By customfields_oo.ans Desc