select a.dname,b.job,sum(b.sal)
from dept a,emp b
where a.deptno = b.deptno
group by rollup(a.dname,b.job)
having grouping(dname) = 0 --过滤掉所有sal的总合SQL>
6 /DNAME JOB SUM(B.SAL)
-------------------- -------------------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 875012 rows selected
from dept a,emp b
where a.deptno = b.deptno
group by rollup(a.dname,b.job)
having grouping(dname) = 0 --过滤掉所有sal的总合SQL>
6 /DNAME JOB SUM(B.SAL)
-------------------- -------------------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 875012 rows selected
from dept a,emp b
where a.deptno = b.deptno
group by rollup(a.dname,b.job)SQL>
5 /DNAME JOB SUM(B.SAL)
-------------------- -------------------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
2902513 rows selected
查詢EMP表中各部門最高工資,最低工資,和其各自所對應的人員姓名,以及人員所在部門的名稱。
查詢結果如下:
DNAME ENAME 最高工資 DNAME ENAME 最低工資
ACCOUNTING KING 5000 ACCOUNTING MILLER 1300
RESEARCH FORD 3000 RESEARCH SMITH 800
RESEARCH SCOTT 3000 RESEARCH SMITH 800
SALES BLAKE 2850 SALES JAMES 950
如何写?
first_value(ENAME) over(partition by a.dname order by sal desc) max_ename,
first_value(sal) over(partition by a.dname order by sal desc) max_sal,
dname,
first_value(ENAME) over(partition by a.dname order by sal) max_ename,
first_value(sal) over(partition by a.dname order by sal) max_sal
from dept a,emp b
where a.deptno = b.deptnoSQL>
9 /DNAME MAX_ENAME MAX_SAL DNAME MAX_ENAME MAX_SAL
-------------------- -------------------- ---------- -------------------- -------------------- ----------
ACCOUNTING KING 5000 ACCOUNTING MILLER 1300
RESEARCH FORD 3000 RESEARCH SMITH 800
SALES BLAKE 2850 SALES JAMES 950
查詢結果如下:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 09-6月 -81 2450 0 10
7369 SMITH CLERK 7902 17-12月-80 800 0 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
怎么让它显示为0啊?谢了