JOIN 语法中的 MySQL 相关子查询 [英] MySQL correlated subquery in JOIN syntax

问题描述

我想通过指定 innertable.id = outertable.id 为内部查询提供 WHERE 条件.但是，MySQL (5.0.45) 报告'where 子句'中的未知列'outertable.id'".这种查询可以吗?

I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible?

内部查询使用 GROUP BY 将行转为列.这可以完全在外部查询中执行，但由于额外的连接可能会产生额外的开销.

The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query, but would possibly incur additional overhead due to the extra joins.

或者，我可以省略内部查询中的 WHERE 条件，而是指定 ON outertable.id = innerquery.id，但它会获取整个内部查询行集以再次加入外部查询，这是低效的.

Alternatively, I can leave off the WHERE condition in the inner query and instead specify an ON outertable.id = innerquery.id, but it would then fetch the entire inner query rowset to join again the outer, which is inefficient.

实际的 SQL 如下所示:

The actual SQL appears below:

select t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension, a.BusinessUnit, a.Department
from swtickets t
inner join swticketposts tp on t.ticketid = tp.ticketid
inner join swusers u on t.userid = u.userid
left join
  (
  select
  cfv.typeid,
  min(case cfv.customfieldid when 1 then cfv.fieldvalue end) as 'PhoneNumber',
  min(case cfv.customfieldid when 3 then cfv.fieldvalue end) as 'Location',
  min(case cfv.customfieldid when 5 then cfv.fieldvalue end) as 'Extension',
  min(case cfv.customfieldid when 8 then cfv.fieldvalue end) as 'BusinessUnit',
  min(case cfv.customfieldid when 9 then cfv.fieldvalue end) as 'Department'
  from swcustomfieldvalues cfv
  where cfv.typeid = t.ticketid
  group by cfv.typeid
  ) as a on 1 = 1
where t.ticketid = 2458;

推荐答案

您的问题的答案是否定的，不可能像您那样引用相关名称.派生表是在外部查询开始评估连接之前由内部查询生成的.所以像 t、tp 和 u 这样的相关名称对于内部查询是不可用的.

The answer to your question is no, it is not possible to reference correlation names as you are doing. The derived table is produced by your inner query before the outer query starts evaluating joins. So the correlation names like t, tp, and u are not available to the inner query.

为了解决这个问题，我建议在内部查询中使用相同的常量整数值，然后在外部查询中使用真实条件而不是 1=1 连接派生表.

To solve this, I'd recommend using the same constant integer value in the inner query, and then join the derived table in the outer query using a real condition instead of 1=1.

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email,
  tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension,
  a.BusinessUnit, a.Department
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
 LEFT OUTER JOIN (
  SELECT cfv.typeid,
    MIN(CASE cfv.customfieldid WHEN 1 THEN cfv.fieldvalue END) AS 'PhoneNumber',
    MIN(CASE cfv.customfieldid WHEN 3 THEN cfv.fieldvalue END) AS 'Location',
    MIN(CASE cfv.customfieldid WHEN 5 THEN cfv.fieldvalue END) AS 'Extension',
    MIN(CASE cfv.customfieldid WHEN 8 THEN cfv.fieldvalue END) AS 'BusinessUnit',
    MIN(CASE cfv.customfieldid WHEN 9 THEN cfv.fieldvalue END) AS 'Department'
  FROM swcustomfieldvalues cfv
  WHERE cfv.typeid = 2458
  GROUP BY cfv.typeid
  ) AS a ON (a.typeid = t.ticketid)
WHERE t.ticketid = 2458;

这篇关于JOIN 语法中的 MySQL 相关子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！