首页 文章

MYSQL CONCAT（）失败但我不知道为什么

提问于
浏览 次
0

需要一些帮助来理解为什么我的concat（）失败以及如何解决它 . 我从来没有使用过concat（），但遇到的情况是我需要从另一个表中获取unit_nbr并将另一个字段连接到它以在主选择中创建一个字段 .

这是我使用的CONCAT（）： CONCAT(b.name, ' - ', unit_nbr) as lease_name

我正在寻找的输出类似于“lease_name”：John Doe - 123单元

这是我的SQL：

SELECT a.lease_id, a.occupant_id, a.unit_id, (SELECT xx.unit_nbr FROM p_unit xx WHERE xx.unit_id = a.unit_id) as unit_nbr, c.name as prop_name, d.p_name, CONCAT(b.name, ' - ', unit_nbr) as lease_name 

FROM o_leases a, p_occupants b, properties c, portfolio d 

WHERE a.occupant_id = b.occupant_id 
AND b.property_id = c.properties_id 
AND c.portfolio_id = d.portfolio_id   
AND a.archived = 1';

谁能帮我？谢谢 .

sql mysql concatenation

2 回答

  • 2

    你不要使用这样的别名 .
    尝试：

    SELECT a.lease_id, a.occupant_id, a.unit_id, xx.unit_nbr, c.name as prop_name, d.p_name, CONCAT(b.name, ' - ', xx.unit_nbr) as lease_name  

FROM o_leases a, p_occupants b, properties c, portfolio d, p_unit xx

WHERE a.occupant_id = b.occupant_id  
AND b.property_id = c.properties_id  
AND c.portfolio_id = d.portfolio_id    
AND a.archived = 1
AND xx.unit_id = a.unit_id;
    回复于
  • 3

    您不能在SELECT中使用字段别名： CONCAT(b.name, ' - ', unit_nbr) （ unit_nbr 是别名）尝试

    SELECT aaa.*, CONCAT(aaa.name, ' - ', aaa.unit_nbr) as lease_name   
FROM
(  
 SELECT a.lease_id, a.occupant_id, a.unit_id, a.name,
 (SELECT xx.unit_nbr FROM p_unit xx WHERE xx.unit_id = a.unit_id) as unit_nbr, 
  c.name as prop_name, d.p_name     

FROM o_leases a, p_occupants b, properties c, portfolio d 

WHERE a.occupant_id = b.occupant_id 
AND b.property_id = c.properties_id 
AND c.portfolio_id = d.portfolio_id   
AND a.archived = '1')aaa;

    另一个解决方案是通过子查询替换CONCAT中的unit_nbr（ ... CONCAT(aaa.name, ' - ', (SELECT xx.unit_nbr FROM p_unit xx WHERE xx.unit_id = a.unit_id)) as lease_name...

    回复于

相关问题