已知ORACLE自带表SCOTT.EMP的数据如下
SQL> select empno,ename,deptno,job,hiredate from emp; EMPNO ENAME DEPTNO JOB HIREDATE
---------- ---------- ---------- --------- ---------
7369 SMITH 20 CLERK 17-DEC-80
7499 ALLEN 30 SALESMAN 20-FEB-81
7521 WARD 30 SALESMAN 22-FEB-81
7566 JONES 20 MANAGER 02-APR-81
7654 MARTIN 30 SALESMAN 28-SEP-81
7698 BLAKE 30 MANAGER 01-MAY-81
7782 CLARK 10 MANAGER 09-JUN-81
7788 SCOTT 20 ANALYST 13-JUL-87
7839 KING 10 PRESIDENT 17-NOV-81
7844 TURNER 30 SALESMAN 08-SEP-81
7876 ADAMS 20 CLERK 13-JUL-87 EMPNO ENAME DEPTNO JOB HIREDATE
---------- ---------- ---------- --------- ---------
7900 JAMES 30 CLERK 03-DEC-81
7902 FORD 20 ANALYST 03-DEC-81
7934 MILLER 10 CLERK 23-JAN-82
题目:创建一查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量?
另一相似题目:
创建一查询,按工作进行分组,求出各组内分别在10,20,30部门工作的雇员的工资的总数,以及各组的工资的合计.
要求:不要使用子查询,变量等.只能使用简单的查询,单行函数,组函数和分组语句
SQL> select empno,ename,deptno,job,hiredate from emp; EMPNO ENAME DEPTNO JOB HIREDATE
---------- ---------- ---------- --------- ---------
7369 SMITH 20 CLERK 17-DEC-80
7499 ALLEN 30 SALESMAN 20-FEB-81
7521 WARD 30 SALESMAN 22-FEB-81
7566 JONES 20 MANAGER 02-APR-81
7654 MARTIN 30 SALESMAN 28-SEP-81
7698 BLAKE 30 MANAGER 01-MAY-81
7782 CLARK 10 MANAGER 09-JUN-81
7788 SCOTT 20 ANALYST 13-JUL-87
7839 KING 10 PRESIDENT 17-NOV-81
7844 TURNER 30 SALESMAN 08-SEP-81
7876 ADAMS 20 CLERK 13-JUL-87 EMPNO ENAME DEPTNO JOB HIREDATE
---------- ---------- ---------- --------- ---------
7900 JAMES 30 CLERK 03-DEC-81
7902 FORD 20 ANALYST 03-DEC-81
7934 MILLER 10 CLERK 23-JAN-82
题目:创建一查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量?
另一相似题目:
创建一查询,按工作进行分组,求出各组内分别在10,20,30部门工作的雇员的工资的总数,以及各组的工资的合计.
要求:不要使用子查询,变量等.只能使用简单的查询,单行函数,组函数和分组语句
题2的工资字段为SAL
(select count(*) cnt from emp ) t
union all
select to_char(HIREDATE,'yyyy') HIREDATE,count(*) from emp
group by to_char(HIREDATE,'yyyy');
---------------------------------
不知道楼主这条要求是基于什么考虑,为啥不能用子查询?难道group by 也不能用?
EMPNO ENAME DEPTNO JOB HIREDATE SALARY
---------- ------ ---------- --------- ----------- ----------
7369 SMITH 20 CLERK 12/17/1980 2000
7499 ALLEN 30 SALESMAN 2/20/1981 1500
7521 WARD 30 SALESMAN 2/22/1981 1000
7566 JONES 20 MANAGER 4/2/1981 3000
7654 MARTIN 30 SALESMAN 9/28/1981 2400
7698 BLAKE 30 MANAGER 5/1/1981 2700
7782 CLARK 10 MANAGER 6/9/1981 2900
7788 SCOTT 20 ANALYST 7/13/1987 2300
7839 KING 10 PRESIDENT 11/17/1981 1200
7844 TURNER 30 SALESMAN 9/8/1981 1800
7876 ADAMS 20 CLERK 7/13/1987 3300
7900 JAMES 30 CLERK 12/3/1981 4000
7902 FORD 20 ANALYST 12/3/1981 2800
7934 MILLER 10 ANALYST 1/23/1982 250014 rows selected1:
select distinct count(*) over() as "All_Count",
to_char(tt.HIREDATE,'yyyy') as "year",
count(*) over(partition by to_char(tt.HIREDATE,'yyyy')) as "count"
from emp tt
where to_char(tt.HIREDATE,'yyyy') in (1980,1981,1982,1983);
All_Count year count
---------- ---- ----------
12 1980 1
12 1981 10
12 1982 1
2:
select distinct tt.job,
tt.DEPTNO,
nvl(sum(tt.salary) over(partition by tt.job,tt.DEPTNO),0) as "Group by 10,20,30",
sum(tt.salary) over(partition by tt.job) as "Total Salary"
from emp tt;JOB DEPTNO Group by 10,20,30 Total Salary
--------- ---------- ----------------- ------------
ANALYST 10 2500 7600
ANALYST 20 5100 7600
CLERK 20 5300 9300
CLERK 30 4000 9300
MANAGER 10 2900 8600
MANAGER 20 3000 8600
MANAGER 30 2700 8600
PRESIDENT 10 1200 1200
SALESMAN 30 6700 67009 rows selected
得到结果:
COUNT(*) TO_C
---------- ----
1 1980
10 1981
1 1982
12最下面的12只是80,81,82人数的总和,而非所有人数的总和,所有人数应为14人