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

问题描述

我想通过指定innertable.id = outsidetable.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 externaltable.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屋！