我用的是Oracle自带的Scott.EMP表。
请问:如何按部门、职位显示/汇总并去除重复列名的scott.emp表,实现如下的2个查询结果
--------------------------------------------------------------------
1. 按部门、职位显示并去除重复列名
deptno job ename sal
--------------------------------------------------------------------
10 CLERK MILLER 1300.00
MANAGER CLARK 2450.00
PRESIDENT KING 5000.00
20 CLERK ADAM 1100.00
SMITH 800.00
ANALYST FORD 3000.00
SCOTT 3000.00
MANAGER JONES 2975.00
30 CLERK JAMES 950.00
MANAGER BLAKE 2850.00
SALESMAN WARD 1250.00
ALLEN 1600.00
MARTIN 1250.00
TURNER 1500.002. 按部门、职位汇总并去除重复列名
deptno job ename sal
--------------------------------------------------------------------
10 CLERK MILLER 1300.00
MANAGER CLARK 2450.00
PRESIDENT KING 5000.00
小计 8750.00
20 CLERK ADAM 1100.00
SMITH 800.00
ANALYST FORD 3000.00
SCOTT 3000.00
MANAGER JONES 2975.00
小计 10875.00
30 CLERK JAMES 950.00
MANAGER BLAKE 2850.00
SALESMAN WARD 1250.00
ALLEN 1600.00
MARTIN 1250.00
TURNER 1500.00
小计 9400.00
合计 29025.00
3. 按部门、职位统计员工个数
deptno job cnt
--------------------------------------------------------
10 CLERK 1
MANAGER 1
PRESIDENT 1
小计 3
20 CLERK 2
ANALYST 2
MANAGER 1
小计 5
30 CLERK 1
MANAGER 1
SALESMAN 4
小计 6
合计 14
请问:如何按部门、职位显示/汇总并去除重复列名的scott.emp表,实现如下的2个查询结果
--------------------------------------------------------------------
1. 按部门、职位显示并去除重复列名
deptno job ename sal
--------------------------------------------------------------------
10 CLERK MILLER 1300.00
MANAGER CLARK 2450.00
PRESIDENT KING 5000.00
20 CLERK ADAM 1100.00
SMITH 800.00
ANALYST FORD 3000.00
SCOTT 3000.00
MANAGER JONES 2975.00
30 CLERK JAMES 950.00
MANAGER BLAKE 2850.00
SALESMAN WARD 1250.00
ALLEN 1600.00
MARTIN 1250.00
TURNER 1500.002. 按部门、职位汇总并去除重复列名
deptno job ename sal
--------------------------------------------------------------------
10 CLERK MILLER 1300.00
MANAGER CLARK 2450.00
PRESIDENT KING 5000.00
小计 8750.00
20 CLERK ADAM 1100.00
SMITH 800.00
ANALYST FORD 3000.00
SCOTT 3000.00
MANAGER JONES 2975.00
小计 10875.00
30 CLERK JAMES 950.00
MANAGER BLAKE 2850.00
SALESMAN WARD 1250.00
ALLEN 1600.00
MARTIN 1250.00
TURNER 1500.00
小计 9400.00
合计 29025.00
3. 按部门、职位统计员工个数
deptno job cnt
--------------------------------------------------------
10 CLERK 1
MANAGER 1
PRESIDENT 1
小计 3
20 CLERK 2
ANALYST 2
MANAGER 1
小计 5
30 CLERK 1
MANAGER 1
SALESMAN 4
小计 6
合计 14
sqlplus 脚本:
sqlplus -s scott/scott <<!set pages 66
set linesize 360
set newpage 0
set feedback off
set space 1break on dept_no skip page -
on jobselect dept_no,job,ename,sal from emp
grou by dept_no,job,ename,sal;
!
exit
sqlplus 脚本:
sqlplus -s scott/scott <<!set pages 66
set linesize 360
set newpage 0
set feedback off
set space 1compute sum of sal on dept_no
compute sum of sal on report
break on dept_no skip page -
on job -
on reportselect dept_no,job,ename,sal from emp
grou by dept_no,job,ename,sal;
!
sqlplus 脚本:
sqlplus -s scott/scott <<!set pages 66
set linesize 360
set newpage 0
set feedback off
set space 1compute sum of cnt on dept_no
compute sum of cnt on report
break on dept_no skip page -
on job -
on reportselect dept_no,job,count(*) cnt from emp
grou by dept_no,job;
!
因为在开发中用其它语句是不行的。