Griffin','.',''),'abcdefghijklmnopqrstuvwxyz',rpad('#',26,'#')),'#',''),' ','.')||'.' from dual
四十二、按字符串中的部分内容排序
db2/oracle/mysql/postgresql
select ename from emp order by substr(ename,length(ename)-1,2) sqlserver
select ename from emp order by substring(ename,len(ename)-1,2)
四十三、按字符串中的数值排序 db2 select
data
from
V
order
by
cast(replace(translate(data,repeat('#',length(data)),replace(translate(data,'##########','0123456789'),'#','')),'#','') as integer)
oracle
select data from V order by to_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))
postgresql
select data from V order by cast(replace(translate(data,replace(translate(data,'0123456789','##########'),'#',''),rpad('#',20,'#')),'#','') as integer)
四十四、根据表中的行创建一个分割列表 db2
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,x.list||','||e.name,e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt
mysql
select deptno,group_concat(ename order by empno separator,',') as emps from emp group by deptno oracle
select deptno,ltrim(sys_connect_by_path(ename,','),',') emps from (select deptno,ename,row_number() over (partition by deptno order by empno) rn,count(*) over (partition by deptno) cnt from emp) where level=cnt start with rn=1 connect by prior deptno=deptno and prior rn=rn-1
postgresql
select deptno,rtrim(max(case when pos=1 then emps else '' end)||max(case when pos=2 then emps
else '' end)||max(case when pos=3 then emps else '' end)||max(case when pos=4 then emps else '' end)||max(case when pos=5 then emps else '' end)||max(case when pos=6 then emps else '' end),',') as emps from (select a.deptno,a.ename||',' as emps,d.cnt,(select count(*) from emp b where a.deptno=b.deptno and b.empno<=empno ) as pos from emp a,(select deptno,count(ename) as cnt from emp group by deptno) d where d.deptno=a.deptno) x group by deptno order by 1 sqlserver
with x (deptno,cnt,list,empno,len) as (select deptno,count(*) over (partition by deptno), cast(ename as varchar(100)),empno,1 from emp union all select x.deptno,x.cnt,cast(x.list+','+e.name as varchar(100)),e.empno,x.len+1 from emp e,x where e.deptno=x.deptno and e.empno>x.empno) select deptno,list from x where len=cnt order by 1
四十五、将分隔数据转换为多值IN列表 db2
select empno,ename,sal,deptno from emp where empno in (select cast(substr(c,2,locate(',',c,2)-2) as integer) empno from (select substr(csv.emps,cast(iter.pos as integer)) as c from (select ','||'7654,7698,7782,7788'||',' emps from t1 ) csv,(select id as pos from t100) iter where iter.pos<=length(csv.emps)) x where length(c)>1 and substr(c,1,1)=',' ) y mysql select
empno,ename,sal,deptno
from
emp
where
empno
in
(select
substring_index(substring_index(list.vals,',',iter.pos),',',-1) empno from (select id pos from t10) as iter,(select '7654,7698,7782,7788' as vals from t1) list where iter.pos<=(length(list.vals)-length(replace(list.vals,',','')))+1) x oracle select
empno,ename,sal,deptno
from
emp
where
empno
in
(select
to_number(rtrim(substr(emps,instr(emps,',',1,iter.pos)+1,instr(emps,',',1,iter.pos+1)-instr(emps,',',1,iter.pos)),',')) emps from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,(select rownum pos from emp) iter where iter.pos<=((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1
postgresql
select empno,ename,sal,deptno from emp where empno in (select cast (empno as integer) as empno from (select split_part(list.vals,',',iter.pos) as empno from (select id as pos from t10) iter,(select ','||'7654,7698,7782,7788'||',' as vals from t1) list iter.pos<=length(list.vals)-length(replace(ist.vals,',',''))) z where length(empno)>0) x
sqlserver
select empno,ename,sal,deptno from emp where empno in (select substring(c,2,charindex(',',c,2)-2) as empno from (select substring(csv.emps,iter.pos,len(csv.emps)) as c from (select ','+'7654,7698,7782,7788'+',' as emps from t1) csv,(select id as pos from t100) iter where iter.pos<=len(csv.emps)) x where len(c)>1 and substring(c,1,1)=',') y
四十六、按字母顺序排列字符串
where
db2
select ename,max(case when pos=1 then c else '' end)||max(case when pos=2 then c else '' end)||max(case when pos=3 then c else '' end)||max(case when pos=4 then c else '' end)||max(case when pos=5 then c else '' end)||max(case when pos=6 then c else '' end) from (select e.ename,cast(substr(e.ename,iter.pos,1) as varchar(100)) c,cast(row_number() over (partition by e.ename order by substr(e.ename,iter.pos,1)) as integer) pos from emp e,(select cast(row_number() over() as integer) pos from emp) iter where iter.pos<=length(e.ename)) x group by ename
mysql
select ename,group_concat(c order by c separator '') from ( select ename,substr(a.ename,iter.pos,1) c from emp a,(select id pos from t10) iter where iter.pos<=length(a.ename)) x group by ename
oracle
select old_name,new_name from (select old_name,replace(sys_connect-by_path(c,' '),' ') new_name from (select e.ename old_name,row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1)) rn,substr(e.ename,iter.pos,1) c from emp e,(select rownum pos from emp) iter where iter.pos<=length(e.ename) order by 1) x start with rn=1 connect by prior rn=rn-1 and prior old_name=old_name) where length(old_name)=length(new_name)
postgresql
必须先遍历每个字符串，先要知道姓名的最大长度
sqlserver
select ename,max(case when pos=1 then c else '' end)+max(case when pos=2 then c else '' end)+max(case when pos=3 then c else '' end)+max(case when pos=4 then c else '' end)+max(case when pos=5 then c else '' end)+max(case when pos=6 then c else '' end) from (select e.ename,substring(e.ename,iter.pos,1) as c,row_number() over (partition by e.ename order by substring(e.ename,iter.pos,1)) as pos from emp e,(select row_number() over(order by ename) as pos from emp) iter where iter.pos<=len(e.ename)) x group by ename
四十七、提取第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 (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