如何在左联接中使用别名?

问题描述

我的MySQL命令有问题

I have a problem in my MySQL command

select upper(file_type) as 'ItemType', ponum as 'Policy Number', office as 'Office', fullname as 'FullName', remarks as 'Remarks', concat(x.id, '-',x.file_type) as 'Identification'
from (select * from active_tb
union
select * from processed_tb) as x
left join filelocation as y on x.identification = y.f_id

尝试执行查询时得到unknown column x.Identification.

首先，我连接两个不同的表，然后选择所需的列，然后分配一个别名.我需要连接标识列.

First I join two different tables, then select the columns I need, then assign an alias. I need to concatenate the identification column.

但是我不能在左联接中使用别名.

But I cannot use the alias in a left join.

推荐答案

只需先在左联接子查询中构建标识列即可.

Just build your identification column in your left join sub query first.

SELECT upper(file_type) AS 'ItemType',
       ponum            AS 'Policy Number',
       office           AS 'Office',
       fullname         AS 'FullName',
       remarks          AS 'Remarks',
       x.identification
FROM   (SELECT *,
               concat(x.id, '-', x.file_type) AS 'identification'
        FROM   active_tb
        UNION
        SELECT *,
               concat(x.id, '-', x.file_type) AS 'identification'
        FROM   processed_tb) AS x
       LEFT JOIN filelocation AS y
              ON x.identification = y.f_id 

这篇关于如何在左联接中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！