MySQL:如何将列别名与特定的 JOIN 子句相关联

Question

我的查询使用几个 LEFT JOIN 从几个表中提取结果。它目前工作正常。但是，我现在必须从同一个表列中获取两个不同的字段(但匹配不同的条件)。我不知道如何确保从正确的 LEFT JOIN 分配正确的列别名。有道理吗？

举个例子...

SELECT table1.user_id, table2.value AS school, table2.value AS language  FROM table1  
LEFT JOIN table2 ON table2.user_id = table1.user_id AND table2.key = 'school'
LEFT JOIN table2 ON table2.user_id = table1.user_id AND table2.key = 'language'

我需要具有相应 JOIN 的列别名。我该怎么做？

作为引用，这是我的原始查询(没有辅助 JOIN):

SELECT wsat_ib.user_id, wpjb_resume.id resume_id, wpjb_resume.firstname, wpjb_resume.lastname, 
     wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score,
     wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, 
     GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, 
     GROUP_CONCAT(wpjb_application.job_id) AS applications FROM  `wsat_ib` 
    LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
    LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
    LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id AND wpjb_field_value.field_id=3
    LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id 
    WHERE (wp_usermeta.meta_key = 'target_employer' AND (wp_usermeta.meta_value = 'public' OR wp_usermeta.meta_value=1523) AND wpjb_resume.is_active =1) AND (wpjb_resume.firstname='Xu' OR wpjb_resume.lastname='Xu') 
    GROUP BY wsat_ib.user_id, resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, wpjb_field_value.value
     ORDER BY overall_score DESC LIMIT 0, 20;

这是我在实现以下解决方案时的早期尝试。我已将 AS school 添加到 JOIN 子句，但尚未为该表添加第二个 JOIN。由于某种原因，此版本不返回任何结果。

SELECT wsat_ib.user_id, wpjb_resume.id resume_id, wpjb_resume.firstname, wpjb_resume.lastname, 
 wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score,
 wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, 
 GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, 
 GROUP_CONCAT(wpjb_application.job_id) AS applications FROM  `wsat_ib` 
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value AS school ON wpjb_resume.id=wpjb_field_value.job_id AND wpjb_field_value.field_id=3
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id 
WHERE (wp_usermeta.meta_key = 'target_employer' AND (wp_usermeta.meta_value = 'public' OR wp_usermeta.meta_value=1523) AND wpjb_resume.is_active =1) AND (wpjb_resume.firstname='Xu' OR wpjb_resume.lastname='Xu') 
GROUP BY wsat_ib.user_id, resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, wpjb_field_value.value
 ORDER BY overall_score DESC LIMIT 0, 20;

Answer 1

您还需要为表设置别名，而不仅仅是列。

SELECT table1.user_id, schools.value AS school, languages.value AS language
FROM table1  
LEFT JOIN table2 AS schools ON schools.user_id = table1.user_id
    AND schools.key = 'school'
LEFT JOIN table2 AS languages ON languages.user_id = table1.user_id
    AND languages.key = 'language'

如果一个表在查询中出现不止一次，您可以将每次出现都视为一个实例。您需要为表的每个实例命名以消除歧义。