select ceil(row_number()over(order by empno)/5.0) grp,
empno,
ename
from emp
1.6. PostgreSQL
1.7. MySQL
1.8. 使用标量子查询为每个EMPNO生成一个序号
1.8.1. sql
select ceil(rnk/5.0) as grp,
empno, ename
from (
select e.empno, e.ename,
(select count(*) from emp d
where e.empno ＜ d.empno)+1 as rnk
from emp e
) x
order by grp
2. 创建预定数目的桶
2.1. 不在乎每个桶里有多少个元素，但需要创建固定数目（数目已知）的桶
2.2. DB2
2.2.1. sql
select mod(row_number()over(order by empno),4)+1 grp,
empno,
ename
from emp
order by 1
2.3. Oracle
2.4. SQL Server
2.5. 使用窗口函数NTILE
2.5.1. sql
select ntile(4)over(order by empno) grp,
empno,
ename
from emp
2.6. PostgreSQL
2.7. MySQL
2.8. 使用自连接基于EMPNO为每一行生成一个序号
2.8.1. sql
select mod(count(*),4)+1 as grp,
e.empno,
e.ename
from emp e, emp d
where e.empno ＞= d.empno
group by e.empno,e.ename
order by 1
select deptno,
repeat('*',count(*)) cnt
from emp
group by deptno
3.3. Oracle
3.4. PostgreSQL
3.5. MySQL
3.6. 使用LPAD函数生成所需的字符串“*”
3.6.1. sql
select deptno,
lpad('*',count(*),'*') as cnt
from emp
group by deptno
3.6.2. sql
select deptno,
lpad('*',count(*)::integer,'*') as cnt
from emp
group by deptno
3.6.2.1. CAST函数调用是必须的，因为PostgreSQL 要求LPAD的参数为整数
3.7. SQL Server
3.7.1. sql
select deptno,
replicate('*',count(*)) cnt
from emp
group by deptno
4. 创建垂直直方图
4.1. 结果集
4.1.1. sql
D10 D20 D30
--- --- ---
*
* *
* *
* * *
* * *
* *
4.2. DB2
4.3. Oracle
4.4. SQL Server
4.5. 使用窗口函数ROW_NUMBER OVER
4.5.1. sql
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
4.6. PostgreSQL
4.7. MySQL
4.8. 使用标量子查询
4.8.1. sql
select max(deptno_10) as d10,
max(deptno_20) as d20,
max(deptno_30) as d30
from (
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno ＜ d.empno ) as rnk
from emp e
) x
group by rnk
order by 1 desc, 2 desc, 3 desc
5. 返回非分组列
5.1. 希望找出每个部门工资最高和最低的员工，同时也希望找出每个职位对应的工资最高和最低的员工
5.2. DB2
5.3. Oracle
5.4. SQL Server
5.5. 窗口函数MAX OVER和MIN OVER
5.5.1. sql
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)
5.6. PostgreSQL
5.7. MySQL
5.8. 使用标量子查询
5.8.1. sql
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 as dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end as job_status
from (
select e.deptno,e.ename,e.job,e.sal,
(select max(sal) from emp d
where d.deptno = e.deptno) as max_by_dept,
(select max(sal) from emp d
where d.job = e.job) as max_by_job,
(select min(sal) from emp d
where d.deptno = e.deptno) as min_by_dept,
(select min(sal) from emp d
where d.job = e.job) as min_by_job
from emp e
) x
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
6. 简单的小计
6.1. 结果集
6.1.1. sql
JOB SAL
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
6.2. DB2
6.3. Oracle
6.4. 使用GROUP BY的ROLLUP
6.4.1. sql
select case grouping(job)
when 0 then job
else 'TOTAL'
end job,
sum(sal) sal
from emp
group by rollup(job)
6.5. PostgreSQL
6.5.1. sql
select job, sum(sal) as sal
from emp
group by job
union all
select 'TOTAL', sum(sal)
from emp
6.6. MySQL
6.7. SQL Server
6.8. 使用WITH ROLLUP构造
6.8.1. sql
select coalesce(job,'TOTAL') job,
sum(sal) sal
from emp
group by job with rollup
7. 所有可能的表达式组合的小计
7.1. 结果集
7.2. DB2
7.2.1. sql
select deptno,
job,
case cast(grouping(deptno) as char(1))||
cast(grouping(job) as char(1))
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'TOTAL FOR TABLE'
end category,
sum(sal)
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
7.3. Oracle
7.3.1. sql
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
7.4. PostgreSQL
7.5. MySQL
7.6. UNION ALL
7.6.1. sql
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
elect deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
elect null,null,'GRAND TOTAL FOR TABLE', sum(sal)
from emp
7.7. SQL Server
7.7.1. sql
select deptno,
job,
case cast(grouping(deptno)as char(1))+
cast(grouping(job)as char(1))
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by deptno,job with cube
order by grouping(job),grouping(deptno)