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
一二一、创建交叉标签来使用sqlserver的pivot操作
select [10] as dept_10,[20] as dept_20,[30] as dept_30,[40] as dept_40 from (select deptno,
empno from emp) driver pivot (count(driver.empno) for driver.deptno in ( [10],[20],[30],[40]
)) as empPivot
一二二、Unpivot交叉标签使用sqlserver的Unpivot操作
select DNAME, CNT from (select [ACCOUNTING] as ACCOUNTING,[SALES] as SALES,[RESEARCH] as
RESEARCH,[OPERATIONS] as OPERATIONS from (select d.dname, e.empno from emp e,dept