1- select job, sum(case when department=20 then salary else 0 end) dept20, sum(decode(department,50,salary,0)) dept50, .. sum(salary) total from tablename group by job; 2- select count(*) total, count(case when hireyear=1995 then 1 else 0 end) "1995", ... from employees;
没看明白. decode类似case when,自己看看帮助就明白了.
--分别如下: SELECT JOB, SUM(DEPT_20) "DEPT 20", SUM(DEPT_50) "DEPT 50", SUM(DEPT_80) "DEPT 80", SUM(DEPT_90) "DEPT 90", MIN(TOTAL) TOTAL FROM (SELECT JOB, DECODE(DEPTNO, 20, SAL) DEPT_20, DECODE(DEPTNO, 50, SAL) DEPT_50, DECODE(DEPTNO, 80, SAL) DEPT_80, DECODE(DEPTNO, 90, SAL) DEPT_90, SUM(SAL) OVER(PARTITION BY JOB) TOTAL FROM SCOTT.EMP) GROUP BY JOB; SELECT COUNT(*) TOTAL, SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1995', 1, 0)) "1995", SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1996', 1, 0)) "1996", SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1997', 1, 0)) "1997", SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1998', 1, 0)) "1998" FROM SCOTT.EMP S;
这种都是固定列的行列转换 max或者sum+decode 或者case when 再加上group by 就可以实现
select job,
sum(case when department=20 then salary else 0 end) dept20,
sum(decode(department,50,salary,0)) dept50,
..
sum(salary) total
from tablename
group by job;
2-
select count(*) total,
count(case when hireyear=1995 then 1 else 0 end) "1995",
...
from employees;
decode类似case when,自己看看帮助就明白了.
--分别如下:
SELECT JOB,
SUM(DEPT_20) "DEPT 20",
SUM(DEPT_50) "DEPT 50",
SUM(DEPT_80) "DEPT 80",
SUM(DEPT_90) "DEPT 90",
MIN(TOTAL) TOTAL
FROM (SELECT JOB,
DECODE(DEPTNO, 20, SAL) DEPT_20,
DECODE(DEPTNO, 50, SAL) DEPT_50,
DECODE(DEPTNO, 80, SAL) DEPT_80,
DECODE(DEPTNO, 90, SAL) DEPT_90,
SUM(SAL) OVER(PARTITION BY JOB) TOTAL
FROM SCOTT.EMP)
GROUP BY JOB;
SELECT COUNT(*) TOTAL,
SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1995', 1, 0)) "1995",
SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1996', 1, 0)) "1996",
SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1997', 1, 0)) "1997",
SUM(DECODE(TO_CHAR(S.HIREDATE, 'yyyy'), '1998', 1, 0)) "1998"
FROM SCOTT.EMP S;
max或者sum+decode 或者case when
再加上group by 就可以实现