from emp e order by 3
五十、生成累乘积 db2/oracle
select empno,ename,sal,exp(sum(ln(sal)) over (order by sal,empno)) as running_prod from emp
where deptno=10
select empno,ename,sal,tmp as running_prod from (select empno,ename,-sal as sal from emp
where deptno=10) model dimension by(row_number() over(order by sal desc) rn) measures(sal,0
tmp,empno,ename) rules(tmp[any]=case when sal[cv()-1] is null then sal[cv()] else tmp[cv()-
1]*sal[cv()] end )
mysql/postgresql/sqlserver
select e.empno,e.ename,e.sal,(select exp(sum(ln(d.sal))) from emp d where d.empno<=e.empno
and e.deptno=d.deptno) as running_prod from emp e where e.deptno=10
五十一、生成累计差
db2/oracle
select ename,sal,sum(case when rn=1 then sal else -sal end) over(order by sal,empno) as
running_diff from (select empno,ename,sal,row_number() over(order by sal,empno) as rn from
emp where deptno=10) x
mysql/postgresql/sqlserver
select a.empno,a.ename,a.sal,(select case when a.empno=min(b.empno) then sum(b.sal) else
sum(-b.sal) end from emp b where b.empno<=a.empno and b.deptno=a.deptno ) as rnk from emp a
where a.deptno=10
五十二、计算模式 db2/sqlserver
select sal from (select sal,dense_rank() over(order by cnt desc) as rnk from (select
sal,count(*) as cnt from emp where deptno=20 group by sal) x) y where rnk=1
oracle
select max(sal) keep(dense_rank first order by cnt desc) sal from (select sal,count(*) cnt
from emp where deptno=20 group by sal)
mysql/postgresql
select sal from emp where deptno=20 group by sal having count(*) >=all(select count(*) from
emp where deptno=20 group by sal)
五十三、计算中间值 db2
select avg(sal) from (select sal,count(*) over() total cast(count(*) over() as desimal)/2
mid,ceil(cast(count(*) over as decimal)/2) next,row_number() over (order by sal) rn from emp
where deptno=20) x where (mod(total,2)=0 and rn in(mid,mid+1)) or (mod(total,2)=1 and
rn=next)
mysql/postgresql
select avg(sal) from (select e.sal from emp e,emp d where e.deptno=d.deptno and e.deptno=20
group by e.sal having sum(case when e.sal=d.sal then 1 else 0 end)>=abs(sum(sign(e.sal- d.sal))))
oracle
select median(sal) from emp where deptno=20
select percentile_cont(0.5) within group(order by sal) from emp where deptno=20
sqlserver
select avg(sal) from (select sal,count(*) over() total,cast(count(*) over() as decimel)/2
mid,ceiling(cast(count(*) over() as decimal)/2) next,row_number() over(order by sal) rn from
emp where deptno=20) x where (total%2=0 and rn in(mid,mid+1)) or(total%2=1 and rn=next)
五十四、求总和的百分比 mysql/postgresql
select (sum(case when deptno=10 then sal end)/sum(sal))*100 as pct from emp
db2/oracle/sqlserver
select distinct (d10/total)*100 as pct from (select deptno,sum(sal) over() total,sum(sal)
over(partition by deptno) d10 from emp) x where deptno=10
五十五、对可空列作聚集
select avg(coalesce(comm,0)) as avg_comm from emp where deptno=30
五十六、计算不包含最大值和最小值的均值
mysql/postgresql
select avg(sal) from emp where sal not in((select min(sal) from emp),(select max(sal) from emp))
db2/oracle/sqlserver
select avg(sal) from (select sal,min(sal) over() min_sal,max(sal) over() max_sal from emp) x
where sal not in(min_sal,max_sal)
五十七、把字母数字串转换为数值 db2
select cast(replace(translate('paul123f321',repeat
('#',26),'abcdefghijklmnopqrstuvwxyz'),'#','') as integer) as num from t1
oracle/postgresql
select cast(replace(translate('paul123f321','abcdefghijklmnopqrstuvwxyz',rpad
('#',26'#')),'#','') as integer ) as num from t1
五十八、更改累计和中的值 db2/oracle
select case when trx='PY' then 'PAYMENT' else 'PURCHASE' end trx_type,amt,sum(case when
trx='PY' then -amt else amt end) over (order by id,amt) as balance from V
mysql/postgresql/sqlserver
select case when v1.trx='PY' then 'PAYMENT' else 'PURCHASE' end as trx_type,v1.amt,(select sum(case when v2.trx='PY' then -v2.amt else v2.amt end) from V v2 where v2.id<=v1.id) as balance from V v1
五十九、增加或减少日，月，年
db2
select hiredate -5 day as hd_minus_5D,hiredate +5 day as hd_plus_5D,hiredate -5 month as
hd_minus_5M,hiredate +5 month as hd_plus_5M,hiredate -5 year as hd_minus_5Y,hiredate +5 year
as hd_plus_5Y from emp where deptno=10 oracle
select hiredate-5 as hd_minus_5D,hiredate+5 as hd_plus_5D,add_months(hiredate,-5) as
hd_minus_5M,add_months(hiredate,5) as hd_plus_5M,add_months(hiredate,-5*12) as
hd_minus_5Y,add_months(hiredate,5*120 as hd_plus_5Y from emp where deptno=10
postgresql
select hiredate-interval '5 day' as hd_minus_5D,hiredate+interval '5 day' as
hd_plus_5D,hiredate-interval '5 month' as hd_minus_5M,hiredate+interval '5 month' as
hd_plus_5M,hiredate-interval '5 year' as hd_minus_5Y,hiredate+interval '5 year' as
hd_plus_5Y from emp where deptno=10
mysql
select hiredate-interval 5 day as hd_minus_5D,hiredate+interval 5 day as
hd_plus_5D,hiredate-interval 5 month as hd_minus_5M,hiredate+interval 5 month as
hd_plus_5M,hiredate-interval 5 year as hd_minus_5Y,hiredate+interval 5 year as hd_plus_5Y
from emp where deptno=10
select date_add(hiredate,interval -5 day) as hd_minus_5D,date_add(hiredate,interval 5 day)
as hd_plus_5D,date_add(hiredate,interval -5 month) as hd_minus_5M,date_add(hiredate,interval
5 month) as hd_plus_5M,date_add(hiredate,interval -5 year) as hd_minus_5Y,date_add
(hiredate,interval 5 year) as hd_plus_5Y from emp where deptno=10
sqlserver
select dateadd(day,-5,hiredate) as hd_minus_5D,dateadd(day,5,hiredate0 as
hd_plus_5D,dateadd(month,-5,hiredate) as hd_minus_5M,dateadd(month,5,hiredate) as
hd_plus_5M,dateadd(year,-5,hiredate) as hd_minus_5Y,dateadd(year,5,hiredate) as hd_plus_5Y
from emp where deptno=10
六十、计算2个日期之间的天数 db2
select days(ward_hd) - days(allen_hd) from (select hiredate as ward_hd from emp where
ename='WARD') x,(select hiredate as allen_hd from emp where ename='ALLEN') y
oracle/postgresql
select ward_hd - allen_hd from (select hiredate as ward_hd from emp where ename='WARD') x,
(select hiredate as allen_hd from emp where ename='ALLEN') y
mysql/sqlserver
select datediff(day,allen_hd,ward_hd) from select hiredate as ward_hd from emp where ename =
'WARD') x,(select hiredate as allen_hd from emp where ename='ALLEN') y
六十一、计算2个日期之间的工作日天数 db2
select sum(case when dayname(jones_hd+t500.id day -1 day) in ('Saturday','Sunday') then 0
else 1 end) as days from (select max(case when ename='BLAKE' then hiredate end) as
blake_hd,max(case when ename='JONES' then hiredate end) as jones_hd from emp where ename in
('BLAKE','JONES') ) x,t500 where t500.id <=blake_hd-jones_hd+1
mysql
select sum(case when date_format(date_add(jones_hd,interval t500.id-1 DAY),'%a') in
('Sat','Sun') then 0 else 1 end) as days from (select max(case when ename='BLAKE' then
hiredate end) as blake_hd,max(case when ename='JONES' then hiredate end) as jones_hd from
emp where ename in ('BLAKE','JONES') ) x,t500 where t500.id<=datediff(blake_hd,jones_hd)+1 oracle
select sum(case when to_char(jones_hd+t500.id-1,'DY') in ('SAT','SUN') then 0 else 1 end) as
days from ( select max(case when ename='BLAKE' then hiredate end ) as blake_hd,max(case when