问题描述

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上

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

解决方案

多了个括号，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;

解决方案二：

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