mysql - 我可以将多个 MySQL 列连接成一个字段吗？

Question

这个问题已经有答案了:

MySQL, Concatenate two columns (6 个回答)

已关闭 4 年前。

是否可以将 billing_address_street、billing_address_city、billing_address_state、billing_address_postalcode、billing_address_country 连接为一个名为“地址”的字段？

地址字段的预期结果如下

3401 Dufferin St, 北约克, ON, M6A 2T9, 加拿大

SELECT a.name                       AS Name, 
       a.billing_address_street     AS Address, 
       a.billing_address_city       AS City, 
       a.billing_address_state      AS State, 
       a.billing_address_postalcode AS Zip, 
       a.billing_address_country    AS Country, 
       a.phone_office               AS PhoneNumber1, 
       a.date_modified              AS DateModify, 
       ac.store_c                   AS Store, 
       e.email_address              AS Email 
FROM   accounts AS a, 
       accounts_cstm AS ac, 
       email_addr_bean_rel AS eb, 
       email_addresses AS e 
WHERE  a.id = ac.id_c 
       AND a.id = eb.bean_id 
       AND eb.bean_id = e.id 
       AND a.phone_office = '6471111234' 
       AND a.deleted != 1 
GROUP  BY a.id 
ORDER  BY a.date_modified 
LIMIT  1 

Answer 1

如果可以的话，请在应用程序层中执行此操作。它可以更好地处理丢失的地址组件等问题。

如果你确实想在数据库层做，那么CONCAT()可以做到:

SELECT a.name                       AS Name,
       CONCAT(
         a.billing_address_street, ',',
         a.billing_address_city, ',',
         a.billing_address_state, ',', 
         a.billing_address_postalcode, ',', 
         a.billing_address_country
       ) AS address, 
       a.phone_office               AS PhoneNumber1, 
       a.date_modified              AS DateModify, 
       ac.store_c                   AS Store, 
       e.email_address              AS Email 
FROM   accounts AS a, 
       accounts_cstm AS ac, 
       email_addr_bean_rel AS eb, 
       email_addresses AS e 
WHERE  a.id = ac.id_c 
       AND a.id = eb.bean_id 
       AND eb.bean_id = e.id 
       AND a.phone_office = '6471111234' 
       AND a.deleted != 1 
GROUP  BY a.id 
ORDER  BY a.date_modified 
LIMIT  1