 # 1. 創建固定大小的數據桶 ## 1.1. 數據放入若幹個大小固定的桶（bucket）里，每個桶的元素個數是 ...
1. 創建固定大小的數據桶
1.1. 數據放入若幹個大小固定的桶（bucket）里，每個桶的元素個數是事先定好的
1.1.1. 針對商值向上取整
1.2. DB2
1.3. Oracle
1.4. SQL Server
1.5. 使用視窗函數ROW_NUMBER OVER
1.5.1. sql
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
3. 創建水平直方圖
3.1. 結果集
3.1.1. sql
DEPTNO CNT
------ ----------
10 ****
20 ********
30 ****
3.2. DB2
3.2.1. sql
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)
