这个报错ERROR: missing FROM-clause entry for table "a"的核心原因很直白：你定义的别名a是嵌套在主查询里的子查询中的，SQL的别名作用域只限于它所在的那个查询层级，主SELECT根本看不到这个别名。

打个比方：就像你在函数内部定义了一个变量，函数外面的代码没法直接调用它，SQL的别名作用域规则差不多就是这个逻辑。

针对你的SQL的两种修改方案 #

方案一：把内层子查询提升为主查询的JOIN表

把原来嵌套在计算子查询里的a和b直接移到主查询的FROM/JOIN部分，这样它们的别名作用域就覆盖了主SELECT，你就能直接引用a.onhold_endtime了。修改后的完整SQL如下：

SELECT DISTINCT 
    woas.workorderid, 
    a.onhold_endtime, 
    (SELECT count(*) FROM generate_series(b.onhold_starttime, a.onhold_endtime - interval '1h', interval '1h') h 
     WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '18:00') AS onhold_difference1
FROM wo_assessment as y 
LEFT JOIN wo_assessment woas ON y.assessmentid = woas.assessmentid
-- 将原来的子查询a作为LEFT JOIN引入主查询
LEFT JOIN (
    SELECT DISTINCT 
        woas_inner.workorderid, 
        timestamp 'epoch' + nth_value(wos.endtime,1) OVER(
            PARTITION BY woas_inner.workorderid 
            ORDER BY wos.endtime ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) /1000 * INTERVAL '1 second' as onhold_endtime 
    FROM wo_status_info wos 
    LEFT JOIN wo_assessment woas_inner ON woas_inner.assessmentid = wos.assessmentid
    WHERE wos.statusid = 1 AND wos.nextstatusid = 2
) a ON a.workorderid = woas.workorderid
-- 将原来的子查询b也作为LEFT JOIN引入
LEFT JOIN (
    SELECT DISTINCT 
        woas_inner.workorderid, 
        timestamp 'epoch' + nth_value(wos.endtime,1) OVER(
            PARTITION BY woas_inner.workorderid 
            ORDER BY wos.endtime ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) /1000 * INTERVAL '1 second' as onhold_starttime 
    FROM wo_status_info wos 
    LEFT JOIN wo_assessment woas_inner ON woas_inner.assessmentid = wos.assessmentid
    WHERE wos.statusid = 2 AND wos.nextstatusid <> 2
) b ON b.workorderid = woas.workorderid
ORDER BY woas.workorderid ASC

注意我给子查询里的wo_assessment加了woas_inner别名，避免和主查询的woas冲突，防止出现歧义。

方案二：用LATERAL子查询返回多个字段

如果你不想调整主查询的JOIN结构，可以用PostgreSQL支持的LATERAL子查询，让内层子查询同时返回onhold_endtime和你需要计算的onhold_difference1，主查询直接从这个子查询的结果里取字段：

SELECT DISTINCT 
    woas.workorderid, 
    sub_result.onhold_endtime, 
    sub_result.onhold_difference1
FROM wo_assessment as y 
LEFT JOIN wo_assessment woas ON y.assessmentid = woas.assessmentid
LEFT JOIN LATERAL (
    SELECT 
        a.onhold_endtime,
        (SELECT count(*) FROM generate_series(b.onhold_starttime, a.onhold_endtime - interval '1h', interval '1h') h 
         WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '18:00') AS onhold_difference1
    FROM (
        SELECT DISTINCT 
            woas_inner.workorderid, 
            timestamp 'epoch' + nth_value(wos.endtime,1) OVER(
                PARTITION BY woas_inner.workorderid 
                ORDER BY wos.endtime ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) /1000 * INTERVAL '1 second' as onhold_endtime 
        FROM wo_status_info wos 
        LEFT JOIN wo_assessment woas_inner ON woas_inner.assessmentid = wos.assessmentid
        WHERE woas_inner.workorderid = woas.workorderid AND wos.statusid = 1 AND wos.nextstatusid = 2
    ) a 
    LEFT JOIN (
        SELECT DISTINCT 
            woas_inner.workorderid, 
            timestamp 'epoch' + nth_value(wos.endtime,1) OVER(
                PARTITION BY woas_inner.workorderid 
                ORDER BY wos.endtime ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) /1000 * INTERVAL '1 second' as onhold_starttime 
        FROM wo_status_info wos 
        LEFT JOIN wo_assessment woas_inner ON woas_inner.assessmentid = wos.assessmentid
        WHERE wos.statusid = 2 AND wos.nextstatusid <> 2
    ) b ON a.workorderid = b.workorderid
) sub_result ON true
ORDER BY woas.workorderid ASC

LATERAL的好处是允许子查询引用主查询中的字段（比如这里的woas.workorderid），同时可以返回多个结果字段给主查询使用。

关键知识点总结 #

SQL中，表别名或者列别名的作用域是定义它的那个查询块——也就是说，内层子查询里的别名，外层查询是访问不到的。要让外层能用到这个别名，要么把定义别名的查询提升到外层的FROM/JOIN中，要么让内层查询把需要的字段一起返回给外层。

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