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

七十三、显示一年中的每季度的第一天和最后一天
db2
select quarter(dy-1 day) QTR,day-3 month Q_start,dy-1 day Q_end from (select (current_date-
(dayofyear(current_date)-1) day +(rn*3) month) dy from (select row_number() over() rn from
emp fetch first 4 rows only ) x ) y

oracle
select rownum qtr,add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,add_months(trunc
(sysdate,'y'),rownum*3)-1 q_end from emp where rownum< =4

postgresql
select to_char(dy,'Q') as QTR,date(date_trunc('month',dy)-(2*interval '1 month')) as
Q_start,dy as Q_end from (select date(dy+((rn*3)*interval '1 month'))-1 as dy from (select
rn,date(date_trunc('year',current_date)) as dy from generate_series(1,4) gs(rn) ) x ) y

mysql
select quarter(adddate(dy,-1)) QTR,date_add(dy,interval -3 month) Q_start,adddate(dy,-1)
Q_end from (select date_add(dy,interval (3*id) month) dy from (select id,adddate
(current_date,-dayofyear(current_date)+1) dy from t500 where id<=4) x ) y

sqlserver
with x (dy,cnt) as (select dateadd(d,-(datepart(dy,getdate())-1),getdate()),1 from t1 union
all select dateadd(m,3,dy),cnt+1 from x where cnt+1<=4) select datepart(q,dateadd(d,-1,dy))
QTR,dateadd(m,-3,dy) Q_start,dateadd(d,-1,dy) Q_end from x order by 1

七十四、显示给定季度中的最开始和最后的日期
db2
select (q_end-2 month) q_start,(q_end+1 month)-1 day q-end from (select date(substr(cast(yrq
as char(4)),1,4)||’-'||rtrim(cast(mod(yrq,10)*3 as char(2))) ||’-1′) q_end from (select
20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1
union all select 20054 yrq from t1) x ) y

oracle
select add_months(q_end,-2) q_start,last_day(q_end) q_end from ( select to_date(substr
(yrq,1,4)||mod(yrq,10)*3,’yyyymm’) q_end from (select 20051 yrq from dual union all select
20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from
dual) x ) y

postgresql
select date(q_end-(2*interval ‘1 month’)) as q_start,date(q_end+interval ‘1 month’-interval
‘1 day’) as q_end from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,’yyyymm’) as q_end
from (select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq
from t1 union all select 20054 yrq from t1) x ) y

mysql
select date_add(adddate(q_end,-day(q_end)+1),interval -2 month) q_start,q_end from (select
last_day(str_to_date(concat(substr(yrq,1,4),mod(yrq,10)*3),’%Y%m’)) q_end from (select 20051
as yrq from t1 union all select 20052 as yrq from t1 union all select 20053 as yrq from t1
union all select 20054 as yrq from t1) x ) y

sqlserver
select dateadd(m,-2,q_end) q_start,dateadd(d,-1,dateadd(m,1,q_end)) q_end from (select cast
(substring(cast(yrq as varchar),1,4)+’-'+cast(yrq%10*3 as varchar)+’-1′ as datetime) q_end
from (select 20051 as yrq from t1 union all select 20052 as yrq from t1 union all select
20053 as yrq from t1 union all select 20054 as yrq from t1) x ) y

七十五、填充丢失的日期
db2
with x (start_date,end_date) as ( select (min(hiredate) dayofyear(min(hiredate)) day +1 day)
start_date,(max(hiredate) dayofyear(max(hiredate)) day +1 day) +1 year end_date from emp
union all select start_date +1 month,end_date from x where (start_date +1 month) select x.start_date mth,count(e.hiredate) num_hired from x left join emp e on
(x.start_date=(e.hiredate-day(hiredate)-1 day)) group by x.start_date order by 1

oracle
with x as (select add_months(start_date,level-1) start_date from (select min(trunc
(hiredate,'y')) start_date,add_months(max(trunc(hiredate,'y')),12) end_date from emp )
connect by level<=months_between(end_date,start_date)) select x.start_date MTH,count
(e.hiredate) num_hired from x,emp e where x.start_date=trunc(e.hiredate(+),'mm') group by
x.start_date order by 1
with x as (select add_months(start_date,level-1) start-date from (select min(trunc
(hiredate,'y')) start_date,add_months(max(trunc(hiredate,'y')),12) end_date from emp )
connect by level <=months_between(end_date,start_date)) select x.start_date MTH,count
(e.hiredate) num_hired from x left join emp e on (x.start_date=trunc(e.hiredate,'mm')) group
by x.start_date order by 1

postgresql
create view v as select cast(extract year from age(last_month,first_month))*12-1 as integer
) as mths from (select cast(date_trunc('year',min(hiredate)) as date ) as first_month,cast
(cast(date_trunc('year',max(hiredate)) as date) + interval '1 year' as date) as last_month
from emp ) x
select y.mth,count(e.hiredate) as num_hired from (select cast(e.start_date+(x.id*interval '1
month') as date) as mth from generate_series (0,(select mths from v)) x(id),(select cast
(date_trunc('year',min(hiredate)) as date) as start_date from emp ) e) y left join emp e on
(y.mth=date_trunc('month',e.hiredate)) group by y.mth order by 1

mysql
select z.mth,count(e.hiredate) num_hired from (select date_add(min_hd,interval t500.id-1
month) mth from (select min_hd,date_add(max_hd,interval 11 month) max_hd from (select
adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,adddate(max(hiredate),-dayofyear
(max(hiredate))+10 max_hd from emp ) x ) y,t500 where date-add(min_hd,interval t500.id-1
month) -1 month),1)) group by z.mth order by 1

sqlserver
with x (start_date,end_date) as (select (min(hiredate) datepart(dy,min(hiredate))+1)
start_date,dateadd(yy,1,max(hiredate) datepart(dy,max(hiredate))+1)) end_date from emp union
all select dateadd(mm,1,start_date),end_date from x where dateadd(mm,1,start_date) select x.start_date mth,count(e.hiredate) num_hired from x left join emp e on
(x.start_date=dateadd(dd,-day(e.hiredate)+1,e.hiredate)) group by x.start_date order by 1

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

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

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

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

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

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

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