求一SQL 分组语句！

Question

表结构数据如下：
SQL> desc emp;
Name    Type         Nullable Default Comments 
------- ------------ -------- ------- -------- 
ENAME   VARCHAR2(20) Y                         
EMPNO   VARCHAR2(20) Y                         
SAL     NUMBER(6,2)  Y                         
DEPTNO  NUMBER(2)    Y                         
JOB     VARCHAR2(20) Y                         
COMM    NUMBER(6,2)  Y                         
EMPDATE DATE         Y                         SQL> select * from emp;ENAME                EMPNO                     SAL DEPTNO JOB                      COMM EMPDATE
-------------------- -------------------- -------- ------ -------------------- -------- -----------
HES                  30                    2700.45     30 MANAGER                500.60 2008-8-26 2
HES                  30                    3700.45     30 MANAGER                500.60 2008-8-26 2
scott                7788                  2000.00      2                        560.00 2008-8-26 2
JOHN                 1587                  1000.00     30                               1987-3-5
HES                  30                    2970.50      3 MANAGER                500.60 2008-8-26 2
MARTIN               30                    1617.50      3 MANAGER                500.60 2008-8-26 2
MARTIN               30                    1617.50      3 MANAGER                500.60 2008-8-26 2
tom                  0101                  3328.00      3 PM                     600.60 1982-5-1
SMITH                0112                  4400.50      3 PG                     600.60 2008-8-26 2
PETER                0105                  4400.50      3 PG                     600.60 2008-8-26 2
JACK                 0103                  4400.50      3 PG                     600.60 2008-8-26 2
PAUL                 0104                  4400.50      3 PG                     600.60 2008-8-26 2
gao                  0102                  1128.00      3 PP                     600.60 2008-8-26 2
CLERK                0106                  1500.00     30 WY                     300.00 2008-8-26 2
ALLEN                1017                  1425.45     30 WY                     100.60 2008-8-26 2
ALLEN                1017                  1425.45     30 WY                     100.60 2008-8-26 2
WARD                 0017                  1470.45     30 WY                     500.60 2008-8-26 2
MARTIN               0011                  1470.45     30 WY                     500.60 2008-8-26 218 rows selectedSQL> 
问题如下：
求 每个部门每个岗位的平均工资，每个部门的平均工资，每个岗位的平均工资。
这3个平均工资，给我弄的有点糊涂，不知道分组之后的sql怎么写了！在线等，谢谢！

Answer 1

如果不要求一条sql出来三个结果可以这样
每个部门每个岗位的平均工资
select depno,job, avg(sal)
from emp
group by depno,job每个部门
select depno, avg(sal)
from emp
group by depno
每个岗位的平均工资。
select job, avg(sal)
from emp
group by job

Answer 2

SELECT DISTINCT DEPTNO,
       JOB,
       AVG(SAL) OVER(PARTITION BY DEPTNO, JOB),
       AVG(SAL) OVER(PARTITION BY DEPTNO),
       AVG(SAL) OVER(PARTITION BY JOB)
  FROM scott.EMP ;

Answer 3

TRY IT ..
SQL> SELECT * FROM EMP;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               1014 rows selected-- 每个部门每个岗位的平均工资
SQL> SELECT DECODE(GROUPING(DEPTNO),1,'--TOTAL--',DEPTNO) "AVG_DEPTNO",
  2         DECODE(GROUPING(JOB)+GROUPING(DEPTNO),1,'--SUBTOTAL--',2,NULL,JOB) "AVG_JOB",
  3         AVG(SAL)
  4    FROM EMP E
  5   GROUP BY ROLLUP(DEPTNO,JOB)
  6    ;AVG_DEPTNO                               AVG_JOB        AVG(SAL)
---------------------------------------- ------------ ----------
10                                       CLERK              1300
10                                       MANAGER            2450
10                                       PRESIDENT          5000
10                                       --SUBTOTAL-- 2916.66666
20                                       CLERK               950
20                                       ANALYST            3000
20                                       MANAGER            2975
20                                       --SUBTOTAL--       2175
30                                       CLERK               950
30                                       MANAGER            2850
30                                       SALESMAN           1400
30                                       --SUBTOTAL-- 1566.66666
--TOTAL--                                             2073.2142813 rows selected-- 每个部门的平均工资 
SQL> SELECT DEPTNO,
  2         AVG(SAL)
  3    FROM EMP E
  4   GROUP BY DEPTNO;DEPTNO   AVG(SAL)
------ ----------
    10 2916.66666
    20       2175
    30 1566.66666-- 每个岗位的平均工资
SQL> SELECT JOB,
  2         AVG(SAL)
  3    FROM EMP E
  4   GROUP BY JOB;JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   2758.33333
PRESIDENT       5000
SALESMAN        1400SQL> 

Answer 4

select decode(grouping(depno),1,'ALL DEPNO',depno) as depno,
decode(grouping(job),1,'ALL JOP',job) as jop, avg(sal) 
from emp 
group by cube(depno,job)

Answer 5

SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY rollup(deptno, job)
/

Answer 6

按区分组:
SELECT DISTINCT DEPTNO,
       JOB,
       AVG(SAL) OVER(PARTITION BY DEPTNO, JOB),
       AVG(SAL) OVER(PARTITION BY DEPTNO),
       AVG(SAL) OVER(PARTITION BY JOB)
  FROM EMP ;