写sql。
SQL> desc emp;
Name Null? Type
------------------------------ -------- -------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) 1) 查出各部门薪水最高的三个员工的部门、姓名和薪水。 select deptn,ename,sal from (select * from emp order by sal desc ) where rownum<=3 2) 查看各部门的薪水合计,要求有总计信息。 select deptn,sum(sal) dept_sum from emp group by deptn
3) 查看各部门和工作职位薪水合计,要求分别有部门和工作职位的总计信息。 select deptn,job,sum(sal) sal_sum from emp group by deptn,job
4) 查看各部门和工作职位薪水合计,要求按部门有百分比信息。
------------------------------------------- 哪位帮我看看,我写的对不对,不对的请指正,最后一个实在是写不出来,请高手帮助
SQL> desc emp;
Name Null? Type
------------------------------ -------- -------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) 1) 查出各部门薪水最高的三个员工的部门、姓名和薪水。 select deptn,ename,sal from (select * from emp order by sal desc ) where rownum<=3 2) 查看各部门的薪水合计,要求有总计信息。 select deptn,sum(sal) dept_sum from emp group by deptn
3) 查看各部门和工作职位薪水合计,要求分别有部门和工作职位的总计信息。 select deptn,job,sum(sal) sal_sum from emp group by deptn,job
4) 查看各部门和工作职位薪水合计,要求按部门有百分比信息。
------------------------------------------- 哪位帮我看看,我写的对不对,不对的请指正,最后一个实在是写不出来,请高手帮助
解决方案 »
- 关于几张表统计的sql求助,很复杂(我是菜鸟,呵呵),大家帮帮看看,谢谢
- 怎么记录没有成功执行的SQL语句
- 创建directory,如果文件路径为中文,则发生错误
- xp下oracle的安装问题?
- ibatis 里面 oraclle报错!
- sqlplus里和oemapp console里启动数据库问题
- 怎么能用PL/SQL Developer 把一个表空间中的表定义生成文本?
- 第一次装oracle提问
- oracle的表的trigger中怎么得到当前insert 或delete 或update的行的数据。
- 请教:哪里有关于ORACLE数据库安装、备份和恢复的文档,请尽快告知网址
- exists的用法 例子
- 什么是pctXXXXX?
4>
select a.deptn,b.jbo,a.sal_sum,b.sal_sum from
(select empno,deptn,sum(sal) sal_sum from emp group by deptn) a
(select empno,job,sum(sql) sal_sum from emp group by job) b
where a.empno=b.empno
3>
select a.deptn,b.jbo,a.sal_sum,b.sal_sum from
(select empno,deptn,sum(sal) sal_sum from emp group by deptn) a,
(select empno,job,sum(sql) sal_sum from emp group by job) b
where a.empno=b.empno
4>
select a.deptn,b.jbo,c.sal/a.sal_sum,c.sal/b.sal_sum from
(select empno,deptn,sum(sal) sal_sum from emp group by deptn) a,
(select empno,job,sum(sql) sal_sum from emp group by job) b,emp c
where a.empno=b.empno and c.empno=b.empno
a.DEPTNO,a.ename,a.sal
from (select DEPTNO,ename,sal,row_number() over(partition by DEPTNO order by sal desc) as pno from emp) a
where a.pno<=3
如果名次可以重复,用rank()
2)select to_char(deptno),sum(sal) from emp group by deptno
union
select '合计',sum(sal) from emp group by deptno
3)select to_char(deptno),sum(sal) from emp group by deptno,job
union
select job ,sum(sal) from emp group by job
union select to_char(deptno),sum(sal) from emp group by deptno
4)select
a.deptno,a.job,
a.sal/b.sal
from
(select deptno,job, sum(sal) as sal from emp group by deptno,job) a,
(select deptno,sum(sal) as sal from emp group by deptno) b
------ ---------- ---------
10 KING 5000.00
20 SCOTT 3000.00
20 FORD 3000.00
第二个:
SQL> select deptno, sum(sal) dept_sum from emp group by deptno order by deptno;DEPTNO DEPT_SUM
------ ----------
10 8750
20 10875
30 9400
第三个:我的理解是这样的
SQL> SELECT DECODE(GROUPING(deptno), 1, '总计', deptno) deptno, sum(sal) sal_sum
2 from emp
3 group by ROLLUP(deptno);DEPTNO SAL_SUM
---------------------------------------- ----------
10 8750
20 10875
30 9400
总计 29025
第四个:
SQL> SELECT DECODE(GROUPING(deptno), 1, '总计', deptno) deptno,
2 sum(sal) sal_sum,
3 to_char((sum(sal) / (select sum(sal) from emp) * 100), '999.99') || '%' 百分比
4 from emp
5 group by ROLLUP(deptno);DEPTNO SAL_SUM 百分比
---------------------------------------- ---------- --------
10 8750 30.15%
20 10875 37.47%
30 9400 32.39%
总计 29025 100.00%
4> -- FYI
SELECT DEPTNO, JOB, SUM(SAL) / SUM_SAL PRECENTS_DEPT
FROM EMP, (SELECT SUM(SAL) SUM_SAL FROM EMP)
GROUP BY DEPTNO, JOB;