九十三、取消重复的值
db2/oracle/sqlserver
select job from (select job,row_number( )over(partition by job order by job) rn from emp) xwhere rn = 1
mysql/postgresql
select distinct job from emp select job from emp group by job
九十四、发现骑士值（某个字段相同的结果的第一个值）
db2/sqlserver
select deptno,ename,sal,hiredate,max(latest_sal)over(partition by deptno) latest_sal from (select deptno,ename,sal,hiredate,case when hiredate = max(hiredate)over(partition by deptno) then sal else 0 end latest_sal from emp) x order by 1, 4 desc
mysql/postgresql
select e.deptno,e.ename,e.sal,e.hiredate,(select max(d.sal) from emp d where d.deptno = e.deptno and d.hiredate =(select max(f.hiredate) from emp f where f.deptno = e.deptno)) as latest_sal from emp e order by 1, 4 desc oracle
select deptno,ename,sal,hiredate,max(sal) keep(dense_rank last order by hiredate) over (partition by deptno) latest_sal from emp order by 1, 4 desc
九十五、生成简单的预测
db2/sqlserver
with nrows(n) as (select 1 from t1 union all select n+1 from nrows where n+1 <= 3)
select id,order_date,process_date,case when nrows.n >= 2 then process_date+1 else null end as verified,case when nrows.n = 3 then process_date+2 else null end as shipped from (select nrows.n id,getdate()+nrows.n as order_date,getdate()+nrows.n+2 as process_date from nrows) orders, nrows order by 1
oracle
with nrows as (select level nfrom dual connect by level <= 3)
select id,order_date,process_date,case when nrows.n >= 2 then process_date+1 else null end as verified,case when nrows.n = 3 then process_date+2 else null end as shipped from (select nrows.n id,sysdate+nrows.n as order_date,sysdate+nrows.n+2 as process_date from nrows) orders, nrows
postgresql
select id,order_date,process_date,case when gs.n >= 2 then process_date+1 else null end as verified,case when gs.n = 3 then process_date+2 else null end as shipped from (select gs.id,current_date+gs.id
as
order_date,current_date+gs.id+2
as
process_date
from
generate_series(1,3) gs (id) ) orders,generate_series(1,3)gs(n)
mysql不支持自动列产生
九十六、合并一个结果集到一行
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,sum(case when deptno=20 then 1 else 0 end) as deptno_20,sum(case when deptno=30 then 1 else 0 end) as deptno_30 from emp
九十七、合并一个结果集到多行
db2/oracle/sqlserver
select max(case when job='CLERK' then ename else null end) as clerks,max(case when job='ANALYST' then ename else null end) as analysts,max(case when job='MANAGER' then ename else null end) as mgrs,max(case when job='PRESIDENT' then ename else null end) as prez,max (case when job='SALESMAN' then ename else null end) as sales from (select job,ename,row_number()over(partition by job order by ename) rn from emp) x group by rn
postgresql/mysql
select max(case when job='CLERK' then ename else null end) as clerks,max(case when job='ANALYST' then ename else null end) as analysts,max(case when job='MANAGER' then ename else null end) as mgrs,max(case when job='PRESIDENT' then ename else null end) as prez,max (case when job='SALESMAN' then ename else null end) as sales from (select e.job,e.ename, (select count(*) from emp d where e.job=d.job and e.empno < d.empno) as rnk from emp e) x group by rnk
九十八、反转合并结果集
select dept.deptno,case dept.deptno when 10 then emp_cnts.deptno_10 when 20 then emp_cnts.deptno_20 when 30 then emp_cnts.deptno_30 end as counts_by_dept from (select sum (case when deptno=10 then 1 else 0 end) as deptno_10,sum(case when deptno=20 then 1 else 0 end) as deptno_20,sum(case when deptno=30 then 1 else 0 end) as deptno_30 from emp)
emp_cnts,(select deptno from dept where deptno <= 30) dept
九十九、反转合并一个结果集到一列
db2/oracle/sqlserver
select case rn when 1 then ename when 2 then job when 3 then cast(sal as char(4)) end empsfrom (select e.ename,e.job,e.sal,row_number()over(partition by e.empno order by e.empno) rn from emp e,(select * from emp where job='CLERK') four_rows where e.deptno=10) x
一〇〇、从结果集中取消重复值
db2/sqlserver
select case when empno=min_empno then deptno else null end deptno,ename from (select deptno,min(empno)over(partition by deptno) min_empno,empno,ename from emp) x
oracle
select to_number(decode(lag(deptno)over(order by deptno),deptno,null,deptno)) deptno, ename from emp
一〇一、反转结果集进行跨行计算
select d20_sal - d10_sal as d20_10_diff,d20_sal - d30_sal as d20_30_diff from (select sum (case when deptno=10 then sal end) as d10_sal,sum(case when deptno=20 then sal end) as d20_sal,sum(case when deptno=30 then sal end) as d30_sal from emp) totals_by_dept
一〇二、创建一系列固定大小的数据
db2/oracle/sqlserver
select ceil(row_number()over(order by empno)/5.0) grp,empno,ename from emp postgresql/mysqlselect 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
一〇三、创建一定数目的水桶 db2
select mod(row_number( )over(order by empno),4)+1 grp,empno,ename from emp order by 1
oracle/sqlserver
select ntile(4)over(order by empno) grp,empno,ename from emp
mysql/postgresql
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
一〇四、创建横向直方图 db2
select deptno,repeat('*',count(*)) cnt from emp group by deptno
oracle/postgresql/mysql
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
sqlserver
select deptno,replicate('*',count(*)) cnt from emp group by deptno
一〇五、创建垂直直方图
db2/oracle/sqlserver
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
postgresql/mysql
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
一〇六、返回非组列
db2/oracle/sqlserver
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)
postgresql/mysql
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 (max_by_dept,max_by_job,min_by_dept,min_by_job)
一〇七、计算简单的小计
db2/oracle
select case grouping(job) when 0 then job else 'TOTAL' end job,sum(sal) sal from emp group by rollup(job)
sqlserver/mysql
select coalesce(job,'TOTAL') job,sum(sal) sal from emp group by job with rollup
postgresql
select job, sum(sal) as sal from emp group by job union all select 'TOTAL', sum(sal) from emp
一〇八、计算小计为所有可能的组合表达 db2
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)
oracle
select deptno,job,case grouping(deptno)||grouping(job) when '00' then 'TOTAL BY DEPT AND
e)
x
where
sal
in