select d.*,ed.count(empno)
from dept d,
(select deptno,count(empno) from emp
group by deptno having count(empno)>=1) ed
where d.deptno=ed.deptno;各位大侠能帮我看看这段代码有什么问题吗?我是初学者,怎么都看不出有什么问题,但是就是报错
from dept d,
(select deptno,count(empno) from emp
group by deptno having count(empno)>=1) ed
where d.deptno=ed.deptno;各位大侠能帮我看看这段代码有什么问题吗?我是初学者,怎么都看不出有什么问题,但是就是报错
SQL> select d.*,ed."COUNT(EMPNO)"
2 from dept d,
3 (select deptno,count(empno) from emp
4 group by deptno having count(empno)>=1) ed
5 where d.deptno=ed.deptno;
DEPTNO DNAME LOC COUNT(EMPNO)
------ -------------- ------------- ------------
30 SALES CHICAGO 6
20 RESEARCH DALLAS 5
10 ACCOUNTING NEW YORK 3
SQL>
如果要使用小写也必须使用"来指定.
SQL> select d.*,ed.count(empno)
2 from dept d,
3 (select deptno,count(empno) from emp
4 group by deptno having count(empno)>=1) ed
5 where d.deptno=ed.deptno;ORA-00904: "ED"."COUNT": invalid identifierSQL> select d.*,ed.num
2 from dept d,
3 (select deptno,count(empno) num from emp
4 group by deptno having count(empno)>=1) ed
5 where d.deptno=ed.deptno;DEPTNO DNAME LOC NUM
------ -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6--给ed表中的列 count(empno) 起一个别名
select d.*,ed.cnt
from dept d,
(select deptno,count(empno) cnt from emp
group by deptno having count(empno)>=1) ed
where d.deptno=ed.deptno;