五大数据库常用SQL比较(五)

九十一、转换列值

db2/sqlserver/mysql/postgresql

select e.ename, e.sal,coalesce((select min(sal) from emp d where d.sal > e.sal),(select min (sal) from emp) ) as forward,coalesce((select max(sal) from emp d where d.sal < e.sal), (select max(sal) from emp)) as rewind from emp e order by 2

oracle

select ename,sal,nvl(lead(sal)over(order by sal),min(sal)over()) forward,nvl(lag(sal)over (order by sal),max(sal)over()) rewind from emp

九十二、排列结果

db2/oracle/sqlserver

select dense_rank() over(order by sal) rnk, sal from emp

mysql/postgresql

select (select count(distinct b.sal) from emp b where b.sal <= a.sal) as rnk, a.sal from emp a

九十三、取消重复的值

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