七十八、判断重复的日期范围
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
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
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
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( )))+ 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