5种数据库sql语句大全(五)

七十六、查找具体单位的时间

db2/mysql

select ename from emp where monthname(hiredate) in ('February','December') or dayname (hiredate) = 'Tuesday'

oracle/postgresql

select ename from emp where rtrim(to_char(hiredate,'month')) in ('february','december') or rtrim(to_char(hiredate,'day')) = 'tuesday'

sqlserver

select ename from emp where datename(m,hiredate) in ('February','December') or datename (dw,hiredate) = 'Tuesday'

七十七、比较2个记录的时间在月、星期上面是否相同

db2

select a.ename||' was hired on the same month and weekday as '||b.ename msg from emp a,emp b where (dayofweek(a.hiredate),monthname(a.hiredate))=(dayofweek(b.hiredate),monthname (b.hiredate)) and a.empno<b.empno order by a.ename

oracle/postgresql

select a.ename||' was hired on the same month and weekday as '||b.ename msg from emp a,emp b where to_char(a.hiredate,'DMON')=to_char(b.hiredate,'DMON') and a.empno<b.empno order by a.ename

mysql

select concat(a.ename,' was hired on the same month and weekday as ',b.ename) msg from emp a,emp b where date_format(a.hiredate,'%w%M')=date_format(b.hiredate,'%w%M') and a.empno<b.empno order by a.ename

sqlserver

select a.ename+' was hired on the same month and weekday as '+b.ename msg from emp a,emp b where datename(dw,a.hiredate)=datename(dw,b.hiredate) and datename(m,a.hiredate)=datename (m,b.hiredate) and a.empno<b.empnoorder by a.ename

 七十八、判断重复的日期范围

db2/postgresql/oracle

select a.empno,a.ename,'project '||b.proj_id||' overlaps project '||a.proj_id as msg from emp_project a,emp_project b where a.empno=b.empno and b.proj_start>=a.proj_start and b.proj_start<=a.proj_end and a.proj_id!=b.proj_id

mysql

select a.empno,a.ename,concat('project ',b.proj_id,' overlaps project ',a.proj_id) as msg from emp_project a,emp_project b where a.empno=b.empno and b.proj_start>=a.proj_start and b.proj_start<=a.proj_end and a.proj_id !=b.proj_id

sqlserver

select a.empno,a.ename,'project '+b.proj_id+' overlaps project '+a.proj_id as msg from emp_project a,emp_project b where a.empno=b.empno and b.proj_start>=a.proj_start and b.proj_start<=a.proj_end and a.proj_id!=b.proj_id

七十九、定位一系列的连续的值

db2/mysql/postgresql/sqlserver

select v1.proj_id,v1.proj_start,v1.proj_end from V v1,V v2 where v1.proj_end = v2.proj_start

oracle

select proj_id,proj_start,proj_end from (select proj_id,proj_start,proj_end,lead(proj_start) over(order by proj_id) next_proj_start from V) where next_proj_start=proj_end

八十、找出结果之间的差异在同一组或区域

db2/mysql/postgresql/sqlserver

select deptno, ename, hiredate, sal,coalesce(cast(sal-next_sal as char(10)), 'N/A') as diff from (select e.deptno,e.ename,e.hiredate,e.sal,(select min(sal) from emp d where d.deptno=e.deptno and d.hiredate =(select min(hiredate) from emp d where e.deptno=d.deptnoand d.hiredate > e.hiredate)) as next_sal from emp e) x

oracle

select deptno, ename, sal, hiredate,lpad(nvl(to_char(sal-next_sal), 'N/A'), 10) diff from (select deptno, ename, sal, hiredate,lead(sal)over(partition by deptno order by hiredate) next_sal from emp )

 八十一、定位一系列值的开头和结尾

db2/mysql/postgresql/sqlserver

create view v2 as select a.*, case when (select b.proj_id from V b where a.proj_start = b.proj_end) is not null then 0 else 1 end as flag from V a select proj_grp,min(proj_start) as proj_start,max(proj_end) as proj_end from (select a.proj_id,a.proj_start,a.proj_end,(select sum(b.flag) from V2 b where b.proj_id <= a.proj_id) as proj_grp from V2 a) x group by proj_grp

oracle

select proj_grp, min(proj_start), max(proj_end) from (select proj_id,proj_start,proj_end,sum(flag)over(order by proj_id) proj_grp from (select proj_id,proj_start,proj_end,case whenlag(proj_end)over(order by proj_id) = proj_start then 0 else 1 end flag from V)) group by proj_grp

 八十二、填充一系列值中丢失的部分

db2

select x.yr, coalesce(y.cnt,0) cnt from (select year(min(hiredate)over( )) - mod(year(min (hiredate)over( )),10) + row_number( )over( )-1 yr from emp fetch first 10 rows only) xleft join (select year(hiredate) yr1, count(*) cnt from emp group by year(hiredate)) y on ( x.yr = y.yr1 )

oracle

select x.yr, coalesce(cnt,0) cnt from (select extract(year from min(hiredate)over( )) - mod (extract(year from min(hiredate)over( )),10) + rownum-1 yr from emp where rownum <= 10 ) x,(select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt from emp group by to_number (to_char(hiredate,'YYYY'))) y where x.yr = y.yr(+) select x.yr, coalesce(cnt,0) cnt from (select extract(year from min(hiredate)over( )) - mod (extract(year from min(hiredate)over( )),10) + rownum-1 yr from emp where rownum <= 10) xleft join (select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt from emp group by on ( x.yr = y.yr )

postgresql/mysql

select y.yr, coalesce(x.cnt,0) as cnt from (select min_year-mod(cast(min_year as int),10)+rn as yr from (select (select min(extract(year from hiredate)) from emp) as min_year,id-1 as rnfrom t10) a) y left join (select extract(year from hiredate) as yr, count(*) as cnt from empgroup by extract(year from hiredate)) x on ( y.yr = x.yr ) sqlserverselect x.yr, coalesce(y.cnt,0) cnt from (select top (10) (year(min(hiredate)over( )) - year (min(hiredate)over( ))%10)+ row_number( )over(order by hiredate)-1 yr from emp) x left join(select year(hiredate) yr, count(*) cnt from emp group by year(hiredate)) y on ( x.yr = y.yr )

八十三、生成连续的数值

db2/sqlserver

with x (id) as (select 1 from t1 union all select id+1 from x where id+1 <= 10)

select * from x with x (id) as (values (1) union all select id+1 from x where id+1 <= 10)

select * from x

oracle

with x as ( select level id from dual connect by level <= 10)

select * from x select array id from dual model dimension by (0 idx) measures(1 array) rules iterate (10) (array[iteration_number] = iteration_number+1)

postgresql

select id from generate_series (1, 10) x(id)

八十四、给结果集标页数

db2/oracle/sqlserver

select sal from (select row_number( ) over (order by sal) as rn,sal from emp) x where rn between 1 and 5 select sal from (select row_number( ) over (order by sal) as rn,sal from emp) x where rn between 6 and 10

mysql/postgresql

select sal from emp order by sal limit 5 offset 0 select sal from emp order by sal limit 5 offset 5

 八十五、从一个表中忽略n列

db2/oracle/sqlserver

select ename from (select row_number( ) over (order by ename) rn,ename from emp) x where mod(rn,2) = 1

mysql/postgresql

select x.ename from (select a.ename,(select count(*) from emp b where b.ename <= a.ename) as rn from emp a) x where mod(x.rn,2) = 1

八十六、当使用外连接时合并或逻辑

db2/mysql/postgresql/sqlserver

select e.ename, d.deptno, d.dname, d.loc from dept d left join emp e on (d.deptno = e.deptno and (e.deptno=10 or e.deptno=20)) order by 2

select e.ename, d.deptno, d.dname, d.loc from dept d left join (select ename, deptno from emp where deptno in ( 10, 20 )) e on ( e.deptno = d.deptno ) order by 2

oracle

select e.ename, d.deptno, d.dname, d.loc from dept d, emp e where d.deptno = e.deptno (+) and d.deptno = case when e.deptno(+) = 10 then e.deptno(+) when e.deptno(+) = 20 then e.deptno(+) end order by 2

select e.ename, d.deptno, d.dname, d.loc from dept d, emp e where d.deptno = e.deptno (+) and d.deptno = decode(e.deptno(+),10,e.deptno(+),20,e.deptno(+)) order by 2 select e.ename, d.deptno, d.dname, d.loc from dept d,( select ename, deptno from emp where deptno in ( 10, 20 )) e where d.deptno = e.deptno (+) order by 2

八十七、判断哪些列是相互作用的

select distinct v1.* from V v1, V v2 where v1.test1 = v2.test2 and v1.test2 = v2.test1 and v1.test1 <= v1.test2

八十八、选择前n个记录

db2/oracle/sqlserver

select ename,sal from (select ename, sal, dense_rank() over (order by sal desc) dr from emp) x where dr <= 5

mysql/postgresql

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

八十九、找出记录中的最低值和最高值

db2/oracle/sqlserver

select ename from (select ename, sal,min(sal)over( ) min_sal,max(sal) over( ) max_sal from emp) x where sal in (min_sal,max_sal)

mysql/postgresql

select ename from emp where sal in ( (select min(sal) from emp),(select max(sal) from emp) )

九十、调查未来的行

db2/mysql/postgresql/sqlserver

select ename, sal, hiredate from (select a.ename, a.sal, a.hiredate,(select min(hiredate) from emp b where b.hiredate > a.hiredate and b.sal > a.sal ) as next_sal_grtr,(select min (hiredate) from emp b where b.hiredate > a.hiredate) as next_hire from emp a) x where next_sal_grtr = next_hire

oracle

select ename, sal, hiredate from (select ename, sal, hiredate,lead(sal)over(order by hiredate) next_sal from emp) where sal < next_sal

九十一、转换列值

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

一〇六、返回非组列

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