sql语句问题。。。。。。。。。。。。。。。。。。。

Question

select count(*) as c
  from (select *
          from (select s.capgrade,
                       (select codename
                          from tb_tpost_code
                         where id = s.mpostid) mpostname,
                       (select codename
                          from tb_tpost_code
                         where id = s.spostid) spostname,
                       r.id,
                       r.username,
                       r.idcard,
                       to_char(r.scoredate, 'yyyy-mm-dd') scoredate,
                       r.score,
                       (select ut.sortname
                          from pxgl_unit ut
                         where ut.unitid = r.unitid) unitname_,
                       r.remark,
                       r.flowsta,
                       r.chksign,
                       r.noexamsign,
                       mainusername estauser,
                       to_char(r.estatime, 'yyyy-mm-dd') estatime,
                       i.itemname,
                       (select capgrade
                          from tb_tpost_userpost
                         where mpostid = s.mpostid
                           and spostid = s.spostid
                           and idcard = r.idcard) usercapgrade
                  from tb_tpost_skillresult  r,
                       tb_tpost_skillitem    i,
                       tb_tpost_skillstandar s
                 where ((r.noexamsign = 1 and to_char(r.estatime, 'yyyy') = 2019) or
                       to_char(scoredate, 'yyyy') = 2019)
                   and i.standarid = s.id
                   and i.id = r.yitemid
                   and i.flowsta = 99
                   and i.chksign = 1
                   and (r.flowsta >= 91)
                   and r.unitid in (1699)
                   and s.spostid = 2
                   and s.capgrade = 4
                 order by s.mpostid, s.spostid, s.capgrade, i.id, r.flowsta) p
         where nvl(capgrade, 0) != nvl((select capgrade
                                         from tb_tpost_userpost
                                        where mpostid = p.mpostid
                                          and spostid = p.spostid
                                          and idcard = p.idcard),
                                       0))

我这个语句哪里有问题，报错是ora-00904:"p"."spostid":无效的标识符，大神帮忙看一下。
单独跑以下代码的时候没问题：

select *
  from (select s.capgrade,
               (select codename from tb_tpost_code where id = s.mpostid) mpostname,
               (select codename from tb_tpost_code where id = s.spostid) spostname,
               r.id,
               r.username,
               r.idcard,
               to_char(r.scoredate, 'yyyy-mm-dd') scoredate,
               r.score,
               (select ut.sortname
                  from pxgl_unit ut
                 where ut.unitid = r.unitid) unitname_,
               r.remark,
               r.flowsta,
               r.chksign,
               r.noexamsign,
               mainusername estauser,
               to_char(r.estatime, 'yyyy-mm-dd') estatime,
               i.itemname,
               (select capgrade
                  from tb_tpost_userpost
                 where mpostid = s.mpostid
                   and spostid = s.spostid
                   and idcard = r.idcard) usercapgrade,
                   s.mpostid,
                   s.spostid
          from tb_tpost_skillresult  r,
               tb_tpost_skillitem    i,
               tb_tpost_skillstandar s
         where ((r.noexamsign = 1 and to_char(r.estatime, 'yyyy') = 2019) or
               to_char(scoredate, 'yyyy') = 2019)
           and i.standarid = s.id
           and i.id = r.yitemid
           and i.flowsta = 99
           and i.chksign = 1
           and (r.flowsta >= 91)
           and r.unitid in (1699)
           and s.spostid = 2
           and s.capgrade = 4
         order by s.mpostid, s.spostid, s.capgrade, i.id, r.flowsta) p
         where
         nvl(capgrade, 0) != nvl((select capgrade from tb_tpost_userpost where mpostid = p.mpostid and spostid = p.spostid and idcard = p.idcard), 0)

Answer 1

在线格式化了一下这个 SQL

可能是最后的子查询表不是 p ，所以 Where 引用的字段不存在。