mysql在连接语句中使用列别名

Question

我有这样的查询:

select
    a.user_id,
    max(IF(a.meta_key = 'address',a.meta_value, NULL)) AS Address,
    max(IF(a.meta_key = 'mobile', a.meta_value, NULL)) AS mobile,
    max(IF(a.meta_key = 'topics', a.meta_value, NULL)) AS topics,
    b.user_email,
    b.user_login,
    b.display_name
from wp_ntusermeta a
inner join wp_ntusers b ON a.user_id = b.ID
where a.user_id in (select user_id from wp_ntusermeta
                    where meta_value like '%editor%')
group by a.user_id

效果很好，我得到了这样的结果

user_id | Address | mobile | topics | user_email    | user_login | display_name
     1  | chennai | 999... | 4      | xx@domain.com | xxx        | xxxyyy

我还有另一个名为 wp_nttopics 的表，其中的列是 topic_id、topic_name。使用此表与现有查询的连接，将结果中的 topics_id 替换为 topic_name。

预期结果:

user_id | Address | mobile  | topics         | user_email    | user_login | display_name   
     1  | chennai | 999.... | **topic_name** | xx@domain.com | xxx        | xxxyyy

Answer 1

将您的整个查询加入主题表:

select user_id, address, mobile,
topic_name as topics,
user_email, user_login, display_name
from (select a.user_id,
   max(IF(a.meta_key = 'address',a.meta_value, NULL)) AS Address,
   max(IF(a.meta_key = 'mobile', a.meta_value, NULL)) AS mobile,
   max(IF(a.meta_key = 'topics', a.meta_value, NULL)) AS topic_id,
   b.user_email,
   b.user_login,
   b.display_name
   from wp_ntusermeta a
   inner join wp_ntusers b ON a.user_id = b.ID
   where a.user_id in (select user_id from wp_ntusermeta
                where meta_value like '%editor%')
   group by a.user_id) data
 join wp_nttopics t on t.topic_id = data.topic_id