MySQL коррелировал подзапрос в синтаксисе JOIN

Я хотел бы предоставить условие WHERE для внутреннего запроса, указав innertable.id = outertable.id. Однако MySQL (5.0.45) сообщает "Неизвестный столбец" outertable.id "в" where clause ". Возможно ли использовать этот тип запроса?

Внутренний запрос - это поворот строк в столбцы с помощью 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;

Ответ 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;

Ответ 2

Вы используете дизайн Entity-Attribute-Value, и в конечном итоге нет возможности сделать это масштабируемым, если вы попытаетесь создать обычные наборы результатов. Не пытайтесь сделать это в одном запросе.

Вместо этого сначала запросите свои нормализованные таблицы:

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  |
+---------------+--------------+

Затем вам необходимо написать код приложения для сопоставления полей результатов с полями объекта приложения в цикле.

Использование таблицы Entity-Attribute-Value таким образом более масштабируемо как с точки зрения производительности, так и для обслуживания кода.

Ответ 3

Я бы написал его с несколькими объединениями. Когда вы говорите, что это "возможно, потребует дополнительных накладных расходов", это говорит мне, что вы не проверяли его, чтобы быть уверенным. Если у вас есть достойные индексы, объединения должны быть довольно тривиальными.

Это также показывает только одну из ловушек общего шаблона проектирования "держать все".

Ответ 4

Мое предложение состояло в том, что вы исключали из соображений эффективности. Например. оставляя предложение where и используя соединение (согласно t.ticketid = a.ticketid)

Вы смогли доказать свои мысли о неэффективности некоторыми конкретными примерами? Я знаю, что вы говорите, но любой метод, который вы используете для каждой строки во внешнем запросе, соединяется с каждой строкой во внутреннем запросе, поэтому, в зависимости от плана выполнения, он может быть не столь неэффективным, как вы подозреваете?

Ответ 5

Я предполагаю, что проблема в том, что "cfv.typeid = t.ticketid" тогда? Мое мышление об этом будет состоять в том, что, хотя MySQL поддерживает коррелированные подзапросы, то, что вы пытаетесь сделать, похоже, что это может потерпеть неудачу в соединении, потому что "внутренний" запрос на самом деле не "внутри" остальной части запроса, как это в предложении WHERE. Но похоже, что вы можете просто взять предложение where из подзапроса и сделать свое условие соединения на a.typeid = t.ticketid.