MySQL中使用JOIN语法的相关子查询

Question

我想通过指定innertable.id = outertable.id在内部查询中提供一个WHERE条件。然而，MySQL（5.0.45）报告“where clause”中未知的列“outertable.id”。这种类型的查询是否可行？

内部查询使用GROUP BY将行旋转为列。虽然外部查询完全可以执行此操作，但由于额外的连接可能会产生额外的开销。

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

下面是实际的SQL：

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;

- Cadaeic

---

我的原始问题是，“这种类型的查询是否可能？”（与MySQL 5.0有关）。更改模式或在应用程序代码上进行修改超出了问题主题。 - Taylor Gerring

Answer 1

你的问题的答案是否定的，像你所做的那样引用相关名称是不可能的。衍生表在外部查询开始评估联接之前由内部查询生成。因此，像“t”、“tp”和“u”这样的相关名称对内部查询不可用。

为解决此问题，我建议在内部查询中使用相同的常量整数值，然后在外部查询中使用实际条件加入导出表，而不是使用“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;

- Bill Karwin

Answer 2

您正在使用实体-属性-值设计，如果尝试生成常规结果集，最终将无法使其可扩展，因此请不要在一次查询中尝试这样做。

相反，请先查询您的规范化表：

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, 
  tp.subject, tp.contents
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
WHERE t.ticketid = 2458;

然后查询您的自定义字段，结果会在结果集的多行中显示：

SELECT cfv.customfieldid, cfv.fieldvalue
FROM swcustomfieldvalues cfv
WHERE cfv.typeid = 2458;

您将在结果集中获得多行，每个自定义字段对应一行：

+---------------+--------------+
| customfieldid | fieldvalue   |
+---------------+--------------+
|             1 | 415-555-1234 |
|             3 | Third office |
|             5 | 123          |
|             8 | Support      |
|             9 | Engineering  |
+---------------+--------------+

接下来，您需要编写应用程序代码，在循环中将结果集字段映射到应用程序对象字段。

使用实体属性值表的方式更具可伸缩性，无论是在性能还是代码维护方面都更加优秀。

- Bill Karwin

---

这个建议的作用与原始问题查询类似（将“where cfv.typeid = t.ticketid”更改为“where cfv.typeid = 2458”）; 不过，它依赖于数据库外部的代码来执行数据透视。 - Taylor Gerring

没错。在实体-属性-值(EAV)的情况下，没有办法在一个查询中使其高效。你应该在应用程序代码中进行数据透视操作。 - Bill Karwin

Answer 3

我会使用多个连接来编写它。当你说“可能会增加额外开销”时，这告诉我你还没有测试过它是否确实如此。如果你有良好的索引，连接应该是相当简单的。

这也展示了通用的“保存所有”表设计模式的一个缺陷。

- Tom H.

---

确实。这种设计被称为实体-属性-值。它在多个方面打破了规范化，而且非常难以使用。 - Bill Karwin

在内部查询（cfv）中使用“WHERE cfv.typeid = 2458”会导致对cfv进行恶意表扫描，因为缺乏索引，然而，省略此条件会使情况变得更糟，导致“Using temporary; Using filesort”，并且在派生查询上进行额外的表扫描。 - Taylor Gerring

我不确定你所说的“内部查询”是什么意思。可能你指的是会有5个新的内部连接。我希望你的CFV表上有一个typeid的索引。我可能会按照typeid和customfieldid的顺序建立聚集索引。 - Tom H

Answer 4

我的建议是基于效率的考虑而被你排除了。例如，可以省略where子句并使用连接（如t.ticketid = a.ticketid）

你能否通过一些具体的例子证明你对低效率的想法？我知道你在说什么，但无论你使用什么方法，外部查询中的每一行都会与内部查询中的每一行进行连接，因此根据执行计划，它可能不像你想象的那样低效？

- AJM

Answer 5

我想问题在于 'cfv.typeid = t.ticketid'，对此我的想法是，虽然MySQL支持相关子查询，但你尝试做的似乎可能会在连接中失败，因为“内部”查询并不像在WHERE子句中那样真正“内嵌”在查询的其余部分中。但看起来你可以将where子句从子查询中取出，并使你的连接条件为a.typeid = t.ticketid。

- chaos

---

抱歉，那是从另一个查询变体中遗留下来的。它与实际产生的错误无关，所以我已从上面的问题中将其移除。 - Taylor Gerring