岁月如灬风
2019-10-28 15:03
采纳率: 76.9%
浏览 146
已采纳

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

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)
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 毕小宝 2019-10-28 15:19
    已采纳

    在线格式化了一下这个 SQL
    图片说明
    可能是最后的子查询表不是 p ,所以 Where 引用的字段不存在。

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题