missyouonnet
missyouonnet
采纳率50%
2015-03-16 00:45 阅读 16.3k

oracle查询语句老是报ORA-00907缺失右括号

--大家好,我有一条oracle查询语句老是报ORA-00907缺失右括号的错误,能不能帮我看一下,谢谢:
--统计毕业班等级考试最佳成绩
select distinct e.xy 学院,
e.xzb 班级,
e.xh 学号,
e.xm 姓名,
a.cet3 英语三级,
b.cet4 英语四级,
c.cet6 英语六级,
d.computer 计算机
from ((select xh, xm, djksmc, max(cj) cet3
from xsdjksb
where djksmc = '英语三级'
Group by xh, xm, djksmc) a,
(select xh, xm, djksmc, max(cj) cet4
from xsdjksb
where djksmc = '英语四级'
Group by xh, xm, djksmc) b,
(select xh, xm, djksmc, max(cj) cet6
from xsdjksb
where djksmc = '英语六级'
Group by xh, xm, djksmc) c,
(select xh, xm, djksmc, max(cj) computer
from xsdjksb
where djksmc like '计算机%'
Group by xh, xm, djksmc) d, xsjbxxb e)
where (a.xh = e.xh or b.xh = e.xh or c.xh = e.xh or d.xh = e.xh)
and ((e.DQSZJ = '2010' AND e.XZ = '5') OR
(e.DQSZJ = '2011' AND e.XZ = '4') OR
(e.DQSZJ = '2012' AND e.XZ = '3'))
AND e.SFZX = '是'
order by e.xy, e.xzb, e.xh, e.xm;

报错落在 djksmc) a,的a上

我确信我的语句所有的左右括号都是匹配的,没有缺失,一定是语句的语法出了问题,请大家帮我把把关,谢谢!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    lzp_lrp WorldMobile 2015-03-16 00:58

    多了个括号,from里的表不能用括号括起来

    SELECT DISTINCT e.xy 学院,
    e.xzb 班级,
    e.xh 学号,
    e.xm 姓名,
    a.cet3 英语三级,
    b.cet4 英语四级,
    c.cet6 英语六级,
    d.computer 计算机
    FROM ( SELECT xh,
    xm,
    djksmc,
    MAX (cj) cet3
    FROM xsdjksb
    WHERE djksmc = '英语三级'
    GROUP BY xh, xm, djksmc) a,
    ( SELECT xh,
    xm,
    djksmc,
    MAX (cj) cet4
    FROM xsdjksb
    WHERE djksmc = '英语四级'
    GROUP BY xh, xm, djksmc) b,
    ( SELECT xh,
    xm,
    djksmc,
    MAX (cj) cet6
    FROM xsdjksb
    WHERE djksmc = '英语六级'
    GROUP BY xh, xm, djksmc) c,
    ( SELECT xh,
    xm,
    djksmc,
    MAX (cj) computer
    FROM xsdjksb
    WHERE djksmc LIKE '计算机%'
    GROUP BY xh, xm, djksmc) d,
    xsjbxxb e
    WHERE (a.xh = e.xh OR b.xh = e.xh OR c.xh = e.xh OR d.xh = e.xh)
    AND ( (e.DQSZJ = '2010' AND e.XZ = '5')
    OR (e.DQSZJ = '2011' AND e.XZ = '4')
    OR (e.DQSZJ = '2012' AND e.XZ = '3'))
    AND e.SFZX = '是'
    ORDER BY e.xy,
    e.xzb,
    e.xh,
    e.xm;

    点赞 评论 复制链接分享
  • missyouonnet missyouonnet 2015-03-16 01:15

    问题解决,确实不报错了,只是执行效率超级低下,因为它要反复比对好几个表格的学号,有什么办法优化下吗?

    点赞 评论 复制链接分享

相关推荐