您的问题的答案是否定的,因为您正在做的过程中不可能引用相关名称。派生表由您的内部查询生成,然后外部查询开始评估联接。因此,相关名称(例如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;