显示直方图-水平
==========================
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
直方图
===============
select row_number( )over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
仅显示直方图
===========================
select max(deptno_10) d10,
max(deptno_20) d20,
max(deptno_30) d30
from (
select row_number( )over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
) x
group by rn
order by 1 desc, 2 desc, 3 desc
返回非GROUP BY列
===========================
select deptno,ename,job,sal,
case when sal = max_by_dept
then 'TOP SAL IN DEPT'
when sal = min_by_dept
then 'LOW SAL IN DEPT'
end dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end job_status
from (
select deptno,ename,job,sal,
max(sal)over(partition by deptno) max_by_dept,
max(sal)over(partition by job) max_by_job,
min(sal)over(partition by deptno) min_by_dept,
min(sal)over(partition by job) min_by_job
from emp
) emp_sals
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
使用ROLLUP显示总计
============================
select case grouping(job)
when 0 then job
else 'TOTAL'
end job,
sum(sal) sal
from emp
group by rollup(job)
汇总(10克):-生成汇总结果中的总计和小计。它只能通过句段与一组查询一起出现。
select empno,sum(sal) from emp group by rollup(empno);
select empno,ename,sum(sal) from emp group by rollup(empno,ename);
select empno,ename,sum(sal) from emp group by rollup(ename,empno);
select empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
select grouping(empno),empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
还要检查
旋转-3From: https://bytes.com/topic/oracle/insights/739011-pivoting-2-a