完整SQL语句如下: select studentinfo.sname from studentinfo where ( select count(*) from gradeinfo where gradeinfo.sname = studentinfo.sname ) > 1;大家可以在sql plus中执行一下,确实出错!
11:56:23 jlanzpa817>select studentinfo.sname 12:40:50 2 from studentinfo 12:40:50 3 where ( 12:40:50 4 select count(*) 12:40:50 5 from gradeinfo 12:40:50 6 where gradeinfo.sname = studentinfo.sname 12:40:50 7 ) > 1; from studentinfo * ERROR 位于第 2 行: ORA-00942: 表或视图不存在 已用时间: 00: 00: 00.50 12:40:50 jlanzpa817>create table studentinfo (sname varchar2(10));表已创建。已用时间: 00: 00: 00.81 12:41:12 jlanzpa817>select studentinfo.sname 12:41:13 2 from studentinfo 12:41:13 3 where ( 12:41:13 4 select count(*) 12:41:13 5 from gradeinfo 12:41:13 6 where gradeinfo.sname = studentinfo.sname 12:41:13 7 ) > 1; from gradeinfo * ERROR 位于第 5 行: ORA-00942: 表或视图不存在 已用时间: 00: 00: 00.20 12:41:13 jlanzpa817>create table gradeinfo (sname varchar2(10));表已创建。已用时间: 00: 00: 00.70 12:41:23 jlanzpa817>select studentinfo.sname 12:41:24 2 from studentinfo 12:41:24 3 where ( 12:41:24 4 select count(*) 12:41:24 5 from gradeinfo 12:41:24 6 where gradeinfo.sname = studentinfo.sname 12:41:24 7 ) > 1;未选定行已用时间: 00: 00: 00.30 12:41:24 jlanzpa817>
还不如修改一下SQL 语句 select a.sname,count(*) from studentinfo A,gradeinfo b where b.sname = a.sname group by a.sname having count(*)>1
select studentinfo.sname from studentinfo where exists( select count(*) from gradeinfo,studentinfo where gradeinfo.sname = studentinfo.sname );
ORACLE中尽量不要用子查询来替代列,用exists比较好。
说句风凉话, 建议用SQL92来写,这样你的程序在很多数据库上都可以用。
错了,子查询不能指定在sql语句的等式左边!
to: liqiangliu(liqiangliu) select studentinfo.sname from studentinfo where exists( select count(*) from gradeinfo where gradeinfo.sname = studentinfo.sname );
同意jlandzpa(欧高黎嘉陈) 但感觉不太必要。
ORA-00936:missing expression请高手给指导!谢谢!
select studentinfo.sname
from studentinfo
where (
select count(*)
from gradeinfo
where gradeinfo.sname = studentinfo.sname
) > 1;大家可以在sql plus中执行一下,确实出错!
12:40:50 2 from studentinfo
12:40:50 3 where (
12:40:50 4 select count(*)
12:40:50 5 from gradeinfo
12:40:50 6 where gradeinfo.sname = studentinfo.sname
12:40:50 7 ) > 1;
from studentinfo
*
ERROR 位于第 2 行:
ORA-00942: 表或视图不存在
已用时间: 00: 00: 00.50
12:40:50 jlanzpa817>create table studentinfo (sname varchar2(10));表已创建。已用时间: 00: 00: 00.81
12:41:12 jlanzpa817>select studentinfo.sname
12:41:13 2 from studentinfo
12:41:13 3 where (
12:41:13 4 select count(*)
12:41:13 5 from gradeinfo
12:41:13 6 where gradeinfo.sname = studentinfo.sname
12:41:13 7 ) > 1;
from gradeinfo
*
ERROR 位于第 5 行:
ORA-00942: 表或视图不存在
已用时间: 00: 00: 00.20
12:41:13 jlanzpa817>create table gradeinfo (sname varchar2(10));表已创建。已用时间: 00: 00: 00.70
12:41:23 jlanzpa817>select studentinfo.sname
12:41:24 2 from studentinfo
12:41:24 3 where (
12:41:24 4 select count(*)
12:41:24 5 from gradeinfo
12:41:24 6 where gradeinfo.sname = studentinfo.sname
12:41:24 7 ) > 1;未选定行已用时间: 00: 00: 00.30
12:41:24 jlanzpa817>
select a.sname,count(*)
from studentinfo A,gradeinfo b
where b.sname = a.sname
group by a.sname
having count(*)>1
from studentinfo
where exists(
select count(*)
from gradeinfo,studentinfo
where gradeinfo.sname = studentinfo.sname
);
建议用SQL92来写,这样你的程序在很多数据库上都可以用。
from studentinfo
where exists(
select count(*)
from gradeinfo
where gradeinfo.sname = studentinfo.sname
);