SQL查询报错：外部查询提示无效列名FHID，请求排查解决

你遇到的"无效列名FHID"错误，原因是子查询中给a.FHID指定了别名'From ID'，导致外部查询无法识别FHID这个列名——子查询返回的对应列名是From ID，而非原始的FHID。

修正方案（二选一即可） #

方案1：外部查询使用子查询定义的别名 #

把外部SELECT的FHID改成[From ID]（因为别名带空格，需要用方括号包裹），同时补充必要的分组语句：

select 
    [From ID] as FHID, 
    sum(case when b.DHSTAT = 'FA' then cnt else 0 end) as 'For Approval',
    sum(case when b.DHSTAT = 'APPRVD' then cnt else 0 end) as 'Approved',
    sum(case when b.DHSTAT = 'CL' then cnt else 0 end) as 'Closed',
    sum(case when b.DHSTAT = 'CRT' then cnt else 0 end) as 'Created',
    sum(case when b.DHSTAT = 'CNL' then cnt else 0 end) as 'Cancelled',
    sum(case when b.DHSTAT = 'RJCT' then cnt else 0 end) as 'Rejected'
from (
    select 
        a.FHID 'From ID' , a.FHVER 'Version', a.FHNAM 'Form Name', 
        convert(Varchar, a.FHCDT, 101) 'Created Date', 
        a.FHCUNAM 'Created by', a.FHDEPT 'Dept.', 
        count(b.DHSTAT) as cnt -- 无需转Varchar，sum会自动处理数值类型
    from 
        tblDWorks_FormHeader a 
    inner join 
        tblDWorks_DataHeader b on a.FHID = b.FHID
    group by 
        a.FHID, a.FHVER, a.FHNAM, a.FHCDT, a.FHCUNAM, a.FHDEPT
) grp
group by [From ID]

方案2：子查询保留FHID列（不修改原始列名） #

如果需要在子查询里同时显示友好名称，可保留FHID列，新增别名列：

select 
    FHID, 
    sum(case when b.DHSTAT = 'FA' then cnt else 0 end) as 'For Approval',
    sum(case when b.DHSTAT = 'APPRVD' then cnt else 0 end) as 'Approved',
    sum(case when b.DHSTAT = 'CL' then cnt else 0 end) as 'Closed',
    sum(case when b.DHSTAT = 'CRT' then cnt else 0 end) as 'Created',
    sum(case when b.DHSTAT = 'CNL' then cnt else 0 end) as 'Cancelled',
    sum(case when b.DHSTAT = 'RJCT' then cnt else 0 end) as 'Rejected'
from (
    select 
        a.FHID, a.FHID as 'From ID' , a.FHVER 'Version', a.FHNAM 'Form Name', 
        convert(Varchar, a.FHCDT, 101) 'Created Date', 
        a.FHCUNAM 'Created by', a.FHDEPT 'Dept.', 
        count(b.DHSTAT) as cnt
    from 
        tblDWorks_FormHeader a 
    inner join 
        tblDWorks_DataHeader b on a.FHID = b.FHID
    group by 
        a.FHID, a.FHVER, a.FHNAM, a.FHCDT, a.FHCUNAM, a.FHDEPT
) grp
group by FHID

额外注意事项 #

1. 原始代码外部查询缺少GROUP BY语句，直接sum会把所有分组数据合并成一行，不符合按FHID统计的需求，修正后的代码已补充分组逻辑。
2. Cast(count(b.DHSTAT) as Varchar)属于冗余操作——count返回数值类型，sum运算时会自动处理，转成字符串反而可能引发隐式转换问题，直接使用count(b.DHSTAT)即可。

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