我正在编写一个用作数据库视图的查询，它现在看起来像这样
SELECT
contact.*,
contact_users.names AS user_names,
contact_status.status_id AS status_id,
status_translation.name AS status_name,
status_translation.lang_id AS lang_id
FROM contacts as contact
LEFT JOIN contact_status AS contact_status ON contact_status.status_id = contact.status
LEFT JOIN contact_status_translation AS status_translation ON status_translation.id = contact.status
LEFT JOIN (
SELECT
contacts_users.contact_id,
string_agg(users.fullname || ', ' || users.id, ' | ') as names
FROM v_contacts_users as contacts_users
LEFT JOIN v_users as users on users.id = contacts_users.user_id
WHERE users.lang_id = status_translation.lang_id
GROUP BY contacts_users.contact_id
) AS contact_users ON contact_users.contact_id = contact.id
WHERE contact.deleted = FALSE
除了WHERE最后一个条件之外，一切都按预期工作LEFT JOIN- 这WHERE users.lang_id = status_translation.lang_id部分说status_translation不能在查询的这一部分中引用？这是为什么？我试图用各种不同的方式引用这个表，但结果仍然相同。事实是，它也v_users被翻译了，所以我只需要从这个表中得到一个结果。感谢您的任何提示
如果要引用以前的列表条目，请在左括号LATERAL之间插入。LEFT JOINFROM
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句