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

一〇六、返回非组列

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 e) x where sal in (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 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)

sqlserver

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)

postgresql/mysql

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 select deptno, null, ‘TOTAL BY DEPT’, sum(sal) from emp group by deptno union all select null,null,’GRAND TOTAL FOR TABLE’, sum(sal) from emp

一〇九、确定行不属于小计

select deptno, job, sum(sal) sal,grouping(deptno) deptno_subtotals,grouping(job) job_subtotals from emp group by cube(deptno,job) select deptno, job, sum(sal) sal,grouping(deptno) deptno_subtotals,grouping(job) job_subtotals from emp group by deptno,job with cube

一一〇、使用情况的表达标记行

select ename,case when job = ‘CLERK’ then 1 else 0 end as is_clerk, case when job = ‘SALESMAN’ then 1 else 0 end as is_sales,case when job = ‘MANAGER’ then 1 else 0 end as is_mgr,case when job = ‘ANALYST’ then 1 else 0 end as is_analyst,case when job = ‘PRESIDENT’ then 1 else 0 end as is_prez from emp order by 2,3,4,5,6

一一一、创建一个稀疏矩阵

select case deptno when 10 then ename end as d10,case deptno when 20 then ename end as d20,case deptno when 30 then ename end as d30,case job when ‘CLERK’ then ename end as clerks,case job when ‘MANAGER’ then ename end as mgrs,case job when ‘PRESIDENT’ then ename end as prez,case job when ‘ANALYST’ then ename end as anals,case job when ‘SALESMAN’ then ename end as sales from emp

一一二、按照单位时间分组行

select ceil(trx_id/5.0) as grp,min(trx_date) as trx_start,max(trx_date) as trx_end,sum(trx_cnt) as total from trx_log group by ceil(trx_id/5.0)

一一三、同时完成聚集不同的组或区域

db2/oracle/sqlserver

select ename,deptno,count(*)over(partition by deptno) deptno_cnt,job,count(*)over(partition by job) job_cnt,count(*)over() total from emp

postgresql/mysql

select e.ename,e.deptno,(select count(*) from emp d where d.deptno = e.deptno) as deptno_cnt,job,(select count(*) from emp d where d.job = e.job) as job_cnt,(select count(*) from emp) as total from emp e

一一四、同时完成一组移动范围的值

db2/oracle

select hiredate,sal,sum(sal)over(order by days(hiredate) range between 90 preceding and current row) spending_pattern from emp e select hiredate,sal,sum(sal)over(order by hiredate range between 90 preceding and current row) spending_pattern from emp e

mysql/postgresql/sqlserver

select e.hiredate,e.sal,(select sum(sal) from emp d where d.hiredate between e.hiredate-90 and e.hiredate) as spending_pattern from emp e order by 1

一一五、反转一个有小计的结果集

db2/oracle

select mgr,sum(case deptno when 10 then sal else 0 end) dept10,sum(case deptno when 20 then sal else 0 end) dept20,sum(case deptno when 30 then sal else 0 end) dept30,sum(case flag when ‘11′ then sal else null end) total from (select deptno,mgr,sum(sal) sal,cast(grouping (deptno) as char(1))||cast(grouping(mgr) as char(1)) flag from emp where mgr is not null group by rollup(deptno,mgr)) x group by mgr

sqlserver

select mgr,sum(case deptno when 10 then sal else 0 end) dept10,sum(case deptno when 20 then sal else 0 end) dept20,sum(case deptno when 30 then sal else 0 end) dept30,sum(case flag when ‘11′ then sal else null end) total from (select deptno,mgr,sum(sal) sal,cast(grouping (deptno) as char(1))+cast(grouping(mgr) as char(1)) flag from emp where mgr is not null group by deptno,mgr with rollup) x group by mgr

一一六、表达父母-孩子关系
db2/oracle/postgresql
select a.ename || ‘ works for ‘ || b.ename as emps_and_mgrs from emp a, emp b where a.mgr =

b.empno

mysql
select concat(a.ename, ‘ works for ‘,b.ename) as emps_and_mgrs from emp a, emp b where a.mgr

= b.empno

sqlserver
select a.ename + ‘ works for ‘ + b.ename as emps_and_mgrs from emp a, emp b where a.mgr =

b.empno

一一七、表达孩子-父母-祖父母之间的关系
db2/sqlserver
with x (tree,mgr,depth) as (select cast(ename as varchar(100)),mgr, 0 from emp where

ename = ‘MILLER’ union all select cast(x.tree+’–>’+e.ename as varchar(100)),e.mgr,

x.depth+1 from emp e, x where x.mgr = e.empno)
select tree leaf___branch___root from x where depth = 2

oracle
select ltrim(sys_connect_by_path(ename,’–>’),’–>’) leaf___branch___root from emp where

level = 3 start with ename = ‘MILLER’ connect by prior mgr = empno

postgresql/mysql
select a.ename||’–>’||b.ename||’–>’||c.ename as leaf___branch___root from emp a, emp b,

emp c where a.ename = ‘MILLER’ and a.mgr = b.empno and b.mgr = c.empno

一一八、创建表的一个层次视图
db2/sqlserver
with x (ename,empno) as (select cast(ename as varchar(100)),empno from emp where mgr is null

union all select cast(x.ename||’ - ‘||e.ename as varchar(100)),e.empno from emp e, x where

e.mgr = x.empno)
select ename as emp_tree from x order by 1

oracle
select ltrim(sys_connect_by_path(ename,’ - ‘),’ - ‘) emp_tree from emp start with mgr is

null connect by prior empno=mgr order by 1

postgresql
select emp_tree from (select ename as emp_tree from emp where mgr is null union select

a.ename||’ - ‘||b.ename from emp a join emp b on (a.empno=b.mgr) where a.mgr is null union

select rtrim(a.ename||’ - ‘||b.ename||’ - ‘||c.ename,’ - ‘) from emp a join emp b on

(a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = ‘KING’ union select

rtrim(a.ename||’ - ‘||b.ename||’ - ‘||c.ename||’ - ‘||d.ename,’ - ‘) from emp a join emp b

on (a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where

a.ename = ‘KING’) x where tree is not null order by 1

mysql
select emp_tree from (select ename as emp_tree from emp where mgr is null union select

concat(a.ename,’ - ‘,b.ename) from emp a join emp b on (a.empno=b.mgr) where a.mgr is null

union select concat(a.ename,’ - ‘,b.ename,’ - ‘,c.ename) from emp a join emp b on

(a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = ‘KING’ union select

concat(a.ename,’ - ‘,b.ename,’ - ‘,c.ename,’ - ‘,d.ename) from emp a join emp b on

(a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where

a.ename = ‘KING’) x where tree is not null order by 1

一一九、找出由给出的父列确定所有子列
db2/sqlserver
with x (ename,empno) as (select ename,empno from emp where ename = ‘JONES’ union all select

e.ename, e.empno from emp e, x where x.empno = e.mgr)
select ename from x

oracle
select ename from emp start with ename = ‘JONES’ connect by prior empno = mgr

postgresql/mysql
select distinct case t100.id when 1 then root when 2 then branch else leaf end as

JONES_SUBORDINATES from (select a.ename as root,b.ename as branch,c.ename as leaf from emp

a, emp b, emp c where a.ename = ‘JONES’ and a.empno = b.mgr and b.empno = c.mgr) x,t100

where t100.id < = 6

create view v1 as select ename,mgr,empno from emp where ename = 'JONES'
create view v2 as select ename,mgr,empno from emp where mgr = (select empno from v1)
create view v3 as select ename,mgr,empno from emp where mgr in (select empno from v2)

select ename from v1 union select ename from v2 union select ename from v3

一二〇、确定哪些行是叶，树枝，或根节点
db2/postgresql/mysql/sqlserver
select e.ename,(select sign(count(*)) from emp d where 0 =(select count(*) from emp f where

f.mgr = e.empno)) as is_leaf,(select sign(count(*)) from emp d where d.mgr = e.empno and

e.mgr is not null) as is_branch,(select sign(count(*)) from emp d where d.empno = e.empno
and d.mgr is null) as is_root from emp e order by 4 desc,3 desc

oracle
select ename,connect_by_isleaf is_leaf,(select count(*) from emp e where e.mgr = emp.empno
and emp.mgr is not null and rownum = 1) is_branch,decode(ename,connect_by_root(ename),1,0)

is_root from emp start with mgr is null connect by prior empno = mgr order by 4 desc, 3 desc