Excel SQL跨表查询求助：为未订购商品报表添加供应商名称列

直接用LEFT JOIN关联SUPPLIER表就能补充供应商名称，以下是修改后的完整查询：

select 
    stockmst.pm_part as PLU, 
    stockmst.pm_actind as ACTIVE,
    stockmst.pm_anal3 as BRAND, 
    stockmst.pm_desc as DESCRIPTION, 
    stockmst.supppurchunit as SIZE, 
    stockmst.pm_retail as PRICE, 
    stockmst.pm_prefsup as SUPPLIER_CODE, -- 保留原代码方便对照
    supplier.su_name as Supp1,
    supplier.su_sname as Supp2
from 
    stockmst
left join supplier 
    on stockmst.pm_prefsup = supplier.su_code -- 这里的supplier.su_code需替换成SUPPLIER表中对应供应商缩写的实际字段名，比如su_abbr或su_id
where 
    stockmst.pm_part not in (select itemcode from grnline 
                             where grnno in (select grnno 
                                             from grnhead 
                                             where dttimestamp > getdate()-180))

关键说明： #

• 用LEFT JOIN而非INNER JOIN：确保那些未设置首选供应商（pm_prefsup为空或无匹配）的商品依然能出现在报表中，不会被过滤。
• 如果觉得表名太长，可给表起别名简化代码，示例如下：

select 
    s.pm_part as PLU, 
    s.pm_actind as ACTIVE,
    s.pm_anal3 as BRAND, 
    s.pm_desc as DESCRIPTION, 
    s.supppurchunit as SIZE, 
    s.pm_retail as PRICE, 
    s.pm_prefsup as SUPPLIER_CODE,
    sup.su_name as Supp1,
    sup.su_sname as Supp2
from 
    stockmst s
left join supplier sup 
    on s.pm_prefsup = sup.su_code
where 
    s.pm_part not in (select itemcode from grnline 
                             where grnno in (select grnno 
                                             from grnhead 
                                             where dttimestamp > getdate()-180))

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