WITH L AS
(SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 9 ),
m AS (
SELECT A.LV AS LV_A,
B.LV AS LV_B,
TO_CHAR(B.LV) || 'x' || TO_CHAR(A.LV) || '=' ||
RPAD(TO_CHAR(A.LV * B.LV), 2, ' ') AS TEXT
FROM L A, L B
WHERE b.LV <= a.LV )
SELECT listagg( m.text,' ') WITHIN GROUP (ORDER BY m.lv_b ) AS 九九表
FROM m
GROUP BY m.lv_a
;
列转行 练习题；
SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00)
FROM (
SELECT
to_char(a.deptno) AS 部门,
MIN(DECODE(a.job,'CLERK',a.avg_sal)) 职员,
MIN(DECODE(a.job,'SALESMAN',a.avg_sal)) 推销员,
MIN(dECODE(a.job,'PRESIDENT',a.avg_sal)) 董事长,
MIN(DECODE(a.job,'MANAGER',a.avg_sal)) 总经理,
MIN(DECODE(a.job,'ANALYST',a.avg_sal)) 分析员
FROM (
SELECT
e.deptno ,
e.job,
ROUND (AVG(sal),5) avg_sal
FROM emp e
GROUP BY e.job,e.deptno
ORDER BY e.deptno
) a
GROUP BY a.deptno
ORDER BY a.deptno
)
UNION ALL
SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00)
FROM(
SELECT
'职位总和' AS 部门,
max(sum(DECODE(job,'CLERK',sal))) AS 职员,
max(sum(DECODE(job,'SALESMAN',sal))) AS 推销员,
max(sum(DECODE(job,'PRESIDENT',sal))) AS 董事长,
max(sum(DECODE(job,'MANAGER',sal))) AS 总经理,
max( sum(DECODE(job,'ANALYST',sal))) AS 分析员
FROM emp
GROUP BY job )
UNION ALL
SELECT 部门,nvl(职员,0.00),nvl(推销员,0.00),nvl(董事长,0.00),nvl(总经理,0.00),nvl(分析员,0.00)
FROM(
SELECT
'平均值' AS 部门,
min(DECODE(job,'CLERK',ROUND (AVG(sal),5))) AS 职员,
min(DECODE(job,'SALESMAN',ROUND (AVG(sal),5))) AS 推销员,
min(DECODE(job,'PRESIDENT',ROUND (AVG(sal),5))) AS 董事长,
min(DECODE(job,'MANAGER',ROUND (AVG(sal),5))) AS 总经理,
min(DECODE(job,'ANALYST',ROUND (AVG(sal),5))) AS 分析员
FROM emp
GROUP BY job );
上例题的简化版 ：
/*
1.先用使用decode()进行行转列查出基础数据作为A表！ ; ;
2.B表对A表中的数据进行汇总然后进行合并;
*/
WITH a AS (
SELECT
to_char(deptno) AS deptno ,
SUM(DECODE(job,'ANALYST',sal,0)) AS ANALYST,
SUM(DECODE(job,'CLERK',sal,0)) AS CLERK,
SUM(DECODE(job,'MANAGER',sal,0)) AS MANAGER,
SUM(DECODE(job,'PRESIDENT',sal,0)) AS PRESIDENT,
SUM(DECODE(job,'SALESMAN',sal,0)) AS SALESMAN ,
SUM(sal) AS 总和
FROM scott.EMP
GROUP BY deptno
ORDER BY 1 ),
b AS (
SELECT '总和',
sum(ANALYST),
sum(CLERK),
sum(MANAGER),
sum(PRESIDENT),
sum(SALESMAN) ,
SUM(总和) FROM a )
SELECT * FROM a
UNION ALL
SELECT * FROM b ;