如何将自定义字符串_foo拼接至关联表foods.tbl的列名末尾

你当前用SELECT *的写法无法单独给foods表的所有列统一加后缀，必须显式指定每一列，给foods的列设置别名来实现需求。

直接用以下SQL语句即可得到你想要的结果：

SELECT 
    company.COMPANY_ID,
    company.COMPANY_NAME,
    company.COMPANY_CITY,
    foods.ITEM_ID AS ITEM_ID_foo,
    foods.ITEM_NAME AS ITEM_NAME_foo,
    foods.ITEM_UNIT AS ITEM_UNIT_foo,
    foods.COMPANY_ID AS COMPANY_ID_foo
FROM company
LEFT JOIN foods ON company.COMPANY_ID = foods.COMPANY_ID;

如果觉得表名太长，也可以给表起短别名简化写法：

SELECT 
    c.COMPANY_ID,
    c.COMPANY_NAME,
    c.COMPANY_CITY,
    f.ITEM_ID AS ITEM_ID_foo,
    f.ITEM_NAME AS ITEM_NAME_foo,
    f.ITEM_UNIT AS ITEM_UNIT_foo,
    f.COMPANY_ID AS COMPANY_ID_foo
FROM company c
LEFT JOIN foods f ON c.COMPANY_ID = f.COMPANY_ID;

这种写法会保留company表的原始列名，同时给foods表的每一列都加上_foo后缀，完全匹配你要的最终结果格式。

内容的提问来源于stack exchange，提问作者Behseini