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

四十七、提取第n个分隔的子串
db2
select substr(c,2,locate(',',c,2)-2) from (select pos,name,substr(name,pos) c,row_number ()

over(partition by name order by length(substr(name,pos)) desc ) rn from (select

','||csv.name||',' as name,cast(iter.pos as integer) as pos from V csv, (select row_number()

over() pos from t100) iter where iter.pos<=length(csv.name)+2) x where length(substr

(name,pos)>1) and substr(substr(name,pos),1,1)=',') y where rn=2

mysql
select name from (select iter.pos,substring_index(substring_index

(src.name,',',iter.pos),',',-1) name from V src,(select id pos from t10) iter,where

iter.pos<=length(src.name)-length(replace(src.name,',',''))) x where pos=2

oracle
select sub from (select iter.pos,src.name,substr(src.name,instr(src.name,',',1,iter.pos)

+1,instr(src.name,',',1,iter.pos+1)-instr(src.name,',',1,iter.pos)-1) sub from (select

','||name||',' as name from V) src,(select rownum pos from emp) iter where iter.pos<length

(src.name)-length(replace(src.name,','))) where pos=2

postgresql
select name from (select iter.pos,split_part(src.name,',',iter.pos) as name from (select id

as pos from t10) iter,(select cast(name as text) as name from v) src where iter.pos<=length

(src.name)-length(replace(src.name,',',''))+1) x where pos=2

sqlserver
select substring(c,2,charindex(',',c,2)-2) from (select pos,name,substring(name,pos,len

(name)) as c,row_number() over(partition by name order by len(substring(name,pos,len(name)))

desc) rn from (select ','+csv.name+',' as name,iter.pos from V csv,(select id as pos from

t100) iter where iter.pos<=len(csv.name)+2) x where len(substring(name,pos,len(name)))>1 and

substring(substring(name,pos,len(name)),1,1)=',') y where rn=2

四十八、分解ip地址
db2
with x (pos,ip) as (values(1,'.92.111.0.222') union all select pos+1,ip from x where

pos+1<=20) select max(case where rn=1 then e end) a,max(case where rn=2 then e end) b,max

(case where rn=3 then e end) c,max(case where rn=4 then e end) d from (select pos,c,d,case

when posstr(d,'.')>0 then substr(d,1,posstr(d,'.')-1) else d end as e,row_number() over

(order by pos desc) rn from (select pos,ip,right(ip,pos) as c,substr(right(ip,pos),2) as d

from x where pos<=length(ip) and substr(right(ip,pos),1,1)='.') x ) y

mysql
select substring_index(substring_index(y.ip,'.',1),'.',-1) a,substring_index

(substring_index(y.ip,'.',2),'.',-1) b,substring_index(substring_index(y.ip,'.',3),'.',-1)

c,substring_index(substring_index(y.ip,'.',4),'.',-1) d from (select '92.111.0.2' as ip from

t1) y

oracle
select ip,substr(ip,1,instr(ip,'.')-1) a,substr(ip,instr(ip,'.')+1,instr(ip,'.',1,2)-instr

(ip,'.')-1) b,substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 )

c,substr(ip,instr(ip,'.',1,3)+1) d from (select '92.111.0.2' as ip from t1)

postgresql
select split_part(y.ip,'.',1) as a,split_part(y.ip,'.',2) as b,split_part(y.ip,'.',3) as

c,split_part(y.ip,'.',4) as d from (select cast('92.111.0.2' as text) as ip from t1) as y

sqlserver
with x (pos,ip) as (select 1 as pos,'.92.111.0.222' as ip from t1 union all select pos+1,ip

from x where pos+1<=20) select max(case when rn=1 then e end) a,max(case when rn=2 then e

end) b,max(case when rn=3 then e end) c,max(case when rn=4 then e end) d from (select

pos,c,d,case when charindex('.',d)>0 then substring(d,1,charindex('.',d)-1) else d end as

e,row_number() over(order by pos desc) rn from (select pos,ip,right(ip,pos) as c,substring

(right(ip,pos),2,len(ip)) as d from x where pos<=len(ip) and substring(right(ip,pos),1,1)

='.') x ) y

四十九、生成累计和
db2/oracle
select ename,sal,sum(sal) over (order by sal,empno) as running_total from emp order by 2

mysql/postgresql/sqlserver
select e.ename,e.sal,(select sum(d.sal) from emp d where d.empno<=e.empno) as running_total

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