5种数据库sql语句大全(六) (完结)

一一六、表达父母-孩子关系
db2/oracle/postgresql
select a.ename || ' works for ' || b.ename as emps_and_mgrs from emp a, emp b where a.mgr =

b.empno

mysql
select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs from emp a, emp b where a.mgr

= b.empno

sqlserver
select a.ename + ' works for ' + b.ename as emps_and_mgrs from emp a, emp b where a.mgr =

b.empno

一一七、表达孩子-父母-祖父母之间的关系
db2/sqlserver
with  x (tree,mgr,depth) as  (select  cast(ename as varchar(100)),mgr, 0 from  emp where 

ename = 'MILLER' union  all select  cast(x.tree+'-->'+e.ename as varchar(100)),e.mgr,

x.depth+1 from  emp e, x where x.mgr = e.empno)
select tree leaf___branch___root from x where depth = 2

oracle
select ltrim(sys_connect_by_path(ename,'-->'),'-->') leaf___branch___root from emp where

level = 3 start with ename = 'MILLER' connect by prior mgr = empno

postgresql/mysql
select a.ename||'-->'||b.ename||'-->'||c.ename as leaf___branch___root from emp a, emp b,

emp c where a.ename = 'MILLER' and a.mgr = b.empno and b.mgr = c.empno

一一八、创建表的一个层次视图
db2/sqlserver
with x (ename,empno) as (select cast(ename as varchar(100)),empno from emp where mgr is null

union all select cast(x.ename||' - '||e.ename as varchar(100)),e.empno from emp e, x where

e.mgr = x.empno)
select ename as emp_tree from x order by 1

oracle
select ltrim(sys_connect_by_path(ename,' - '),' - ') emp_tree from emp start with mgr is

null connect by prior empno=mgr order by 1

postgresql
select emp_tree from  (select ename as emp_tree from  emp where mgr is null union select

a.ename||' - '||b.ename from emp a join emp b on (a.empno=b.mgr) where a.mgr is null union

select rtrim(a.ename||' - '||b.ename||' - '||c.ename,' - ') from emp a join emp b on

(a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = 'KING' union select

rtrim(a.ename||' - '||b.ename||' - '||c.ename||' - '||d.ename,' - ') from emp a join emp b

on (a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where

a.ename = 'KING') x where tree is not null order by 1

mysql
select emp_tree from (select ename as emp_tree from emp where mgr is null union select

concat(a.ename,' - ',b.ename) from emp a join emp b on (a.empno=b.mgr) where a.mgr is null 

union select concat(a.ename,' - ',b.ename,' - ',c.ename) from emp a join emp b on

(a.empno=b.mgr) left join emp c on (b.empno=c.mgr) where a.ename = 'KING' union select

concat(a.ename,' - ',b.ename,' - ',c.ename,' - ',d.ename) from emp a join emp b on

(a.empno=b.mgr) join emp c on (b.empno=c.mgr) left join emp d on (c.empno=d.mgr) where

a.ename = 'KING') x where tree is not null order by 1

一一九、找出由给出的父列确定所有子列
db2/sqlserver
with x (ename,empno) as (select ename,empno from emp where ename = 'JONES' union all select

e.ename, e.empno from emp e, x where x.empno = e.mgr)
select ename from x

oracle
select ename from emp start with ename = 'JONES' connect by prior empno = mgr

postgresql/mysql
select distinct  case t100.id  when 1 then root when 2 then branch  else  leaf  end as

JONES_SUBORDINATES from (select a.ename as root,b.ename as branch,c.ename as leaf from emp

a, emp b, emp c where a.ename = 'JONES' and a.empno = b.mgr and b.empno = c.mgr) x,t100

where t100.id <= 6

create view v1 as select ename,mgr,empno from emp where ename = 'JONES'
create view v2 as select ename,mgr,empno from emp where mgr = (select empno from v1)
create view v3 as select ename,mgr,empno from emp where mgr in (select empno from v2)

select ename from v1 union select ename from v2 union select ename from v3

一二〇、确定哪些行是叶，树枝，或根节点
db2/postgresql/mysql/sqlserver
select e.ename,(select sign(count(*)) from emp d where 0 =(select count(*) from emp f where

f.mgr = e.empno)) as is_leaf,(select sign(count(*)) from emp d where d.mgr = e.empno and

e.mgr is not null) as is_branch,(select sign(count(*)) from emp d where d.empno = e.empno
 and d.mgr is null) as is_root from emp e order by 4 desc,3 desc

oracle
select ename,connect_by_isleaf is_leaf,(select count(*) from emp e where e.mgr = emp.empno
 and emp.mgr is not null and rownum = 1) is_branch,decode(ename,connect_by_root(ename),1,0)

is_root from emp start with mgr is null connect by prior empno = mgr order by 4 desc, 3 desc

一二一、创建交叉标签来使用sqlserver的pivot操作
select [10] as dept_10,[20] as dept_20,[30] as dept_30,[40] as dept_40 from (select deptno,

empno from emp) driver pivot (count(driver.empno) for driver.deptno in ( [10],[20],[30],[40]

)) as empPivot

一二二、Unpivot交叉标签使用sqlserver的Unpivot操作
select DNAME, CNT from (select [ACCOUNTING] as ACCOUNTING,[SALES]  as SALES,[RESEARCH]   as

RESEARCH,[OPERATIONS] as OPERATIONS from (select d.dname, e.empno from emp e,dept d where

e.deptno=d.deptno) driver pivot (count(driver.empno) for driver.dname in ([ACCOUNTING],

[SALES],[RESEARCH],[OPERATIONS])) as empPivot)  new_driver unpivot (cnt for dname in

(ACCOUNTING,SALES,RESEARCH,OPERATIONS)) as un_pivot

一二三、用oracle的模型之句进行转换结果集
select max(d10) d10,max(d20) d20,max(d30) d30 from (select d10,d20,d30 from ( select deptno,

count(*) cnt from emp group by deptno ) model dimension by(deptno d) measures(deptno, cnt

d10, cnt d20, cnt d30) rules(d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,
d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,d30[any] = case when deptno

[cv()]=30 then d30[cv()] else 0 end))

一二四、提取从固定地点的一个字符串的元素
create view V as select 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg from dual union all select

'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg from dual union all select 'call:

[F_GET_ROWS()]b1:[ROSEWOOD…SIR]b2:[44400002]77.90xxxxx' msg  from dual union all select

'film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx' msg from dual

select substr(msg,instr(msg,'[',1,1)+1,instr(msg,']',1,1)-instr(msg,'[',1,1)-1) first_val,
substr(msg,instr(msg,'[',1,2)+1,instr(msg,']',1,2)-instr(msg,'[',1,2)-1) second_val,substr

(msg,instr(msg,'[',-1,1)+1,instr(msg,']',-1,1)-instr(msg,'[',-1,1)-1) last_val from V

一二五、找出一年中的天数（oracle的替代解决方案）
select 'Days in 2005: '||to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD') as report from

dual union all select 'Days in 2004: '||to_char(add_months(trunc(to_date('01-SEP-

2004'),'y'),12)-1,'DDD') from dual

一二六、搜索字母数字的混合字符串
with v as (select 'ClassSummary' strings from dual union select '3453430278' from dual union

select 'findRow 55'  from dual union select '1010 switch'  from dual union select '333' 

from dual union select 'threes'   from dual)

select strings from (select strings,translate

(strings,'abcdefghijklmnopqrstuvwxyz0123456789',rpad('#',26,'#')||rpad('*',10,'*'))

translated from v) x where instr(translated,'#') > 0 and instr(translated,'*') > 0

一二七、使用oracle转换整个数字为二进制
select ename,sal,(select bin from dual model dimension by ( 0 attr ) measures ( sal num,

cast(null as varchar2(30)) bin,'0123456789ABCDEF' hex) rules iterate (10000) until (num[0]

<= 0) (bin[0] = substr(hex[cv()],mod(num[cv( )],2)+1,1)||bin[cv( )],num[0] = trunc(num[cv(

)]/2))) sal_binary from emp

一二八、pivot一排名结果集
select max(case grp when 1 then rpad(ename,6) ||' ('|| sal ||')' end) top_3,max(case grp

when 2 then rpad(ename,6) ||' ('|| sal ||')' end) next_3,max(case grp when 3 then rpad

(ename,6) ||' ('|| sal ||')' end) rest from (select ename,sal,rnk,case when rnk <= 3 then 1

when rnk <= 6 then 2 else 3 end grp,row_number( )over (partition by case when rnk <= 3 then

1 when rnk <= 6 then 2 else 3 end order by sal desc, ename) grp_rnk from (select ename,sal,
dense_rank( )over(order by sal desc) rnk from emp) x) y group by grp_rnk

一二九、添加一个栏标题到一个双重pivot结果集
create table IT_research (deptno number, ename varchar2(20))

insert into IT_research values (100,'HOPKINS')
insert into IT_research values (100,'JONES')
insert into IT_research values (100,'TONEY')
insert into IT_research values (200,'MORALES')
insert into IT_research values (200,'P.WHITAKER')
insert into IT_research values (200,'MARCIANO')
insert into IT_research values (200,'ROBINSON')
insert into IT_research values (300,'LACY')
insert into IT_research values (300,'WRIGHT')
insert into IT_research values (300,'J.TAYLOR')

create table IT_apps (deptno number, ename varchar2(20))

insert into IT_apps values (400,'CORRALES')
insert into IT_apps values (400,'MAYWEATHER')
insert into IT_apps values (400,'CASTILLO')
insert into IT_apps values (400,'MARQUEZ')
insert into IT_apps values (400,'MOSLEY')
insert into IT_apps values (500,'GATTI')
insert into IT_apps values (500,'CALZAGHE')
insert into IT_apps values (600,'LAMOTTA')
insert into IT_apps values (600,'HAGLER')
insert into IT_apps values (600,'HEARNS')
insert into IT_apps values (600,'FRAZIER')
insert into IT_apps values (700,'GUINN')
insert into IT_apps values (700,'JUDAH')
insert into IT_apps values (700,'MARGARITO')

select max(decode(flag2,0,it_dept)) research,max(decode(flag2,1,it_dept)) apps from (select

sum(flag1)over(partition by flag2 order by flag1,rownum) flag,it_dept, flag2 from (select 1

flag1, 0 flag2,decode(rn,1,to_char(deptno),' '||ename) it_dept from (select x.*, y.id,

row_number( )over(partition by x.deptno order by y.id) rn from (select deptno,ename,count

(*)over(partition by deptno) cnt from it_research) x,(select level id from dual connect by

level <= 2) y) where rn <= cnt+1 union all select 1 flag1, 1 flag2,decode(rn,1,to_char

(deptno),' '||ename) it_dept from (select x.*, y.id,row_number( )over(partition by x.deptno

order by y.id) rn from (select deptno,ename,count(*)over(partition by deptno) cnt from

it_apps) x,(select level id from dual connect by level <= 2) y) where rn <= cnt+1) tmp1)

tmp2 group by flag

一三〇、在oracle中转换一个标子查询到一个符合子查询
create type generic_obj as object (val1 varchar2(10),val2 varchar2(10),val3 date);

select x.deptno,x.ename,x.multival.val1 dname,x.multival.val2 loc,x.multival.val3 today from

(select e.deptno,e.ename,e.sal,(select generic_obj(d.dname,d.loc,sysdate+1) from dept d 

where e.deptno=d.deptno) multival from emp e) x

一三一、解析系列化的数据转化为行
create view V as select 'entry:stewiegriffin:lois:brian:' strings from dual union all select

'entry:moe::sizlack:' from dual union all select 'entry:petergriffin:meg:chris:' from dual 

union all select 'entry:willie:' from dual union all select

'entry:quagmire:mayorwest:cleveland:'  from dual union all select 'entry:::flanders:' from

dual union all select 'entry:robo:tchi:ken:' from dual

with cartesian as (select level id from dual connect by level <= 100) 
select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,max(decode(id,2,substr

(strings,p1+1,p2-1))) val2,max(decode(id,3,substr(strings,p1+1,p2-1))) val3 from (select

v.strings,c.id,instr(v.strings,':',1,c.id) p1,instr(v.strings,':',1,c.id+1)-instr

(v.strings,':',1,c.id) p2 from v, cartesian c where c.id <= (length(v.strings)-length

(replace(v.strings,':')))-1) group by strings order by 1

一三二、计算相对的总百分比
select job,num_emps,sum(round(pct)) pct_of_all_salaries from (select job,count(*)over

(partition by job) num_emps,ratio_to_report(sal)over()*100 pct from emp) group by

job,num_emps

一三三、从oracle创建csv输出
select deptno,list from (select * from (select deptno,empno,ename,lag(deptno)over(partition

by deptno order by empno) prior_deptno from emp) model dimension by (deptno,row_number()

over(partition by deptno order by empno) rn) measures(ename,prior_deptno,cast(null as

varchar2(60)) list,count(*)over(partition by deptno) cnt,row_number()over(partition by

deptno order by empno) rnk) rules (list[any,any]order by deptno,rn = case when prior_deptno

[cv(),cv( )] is null then ename[cv( ),cv( )] else ename[cv( ),cv( )]||','||list[cv(),rnk[cv(

),cv( )]-1] end)) where cnt = rn

一三四、找出文本不匹配的格局(oracle)
select emp_id, text from employee_comment where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-.

][0-9]{4}') and regexp_like(regexp_replace(text,'[0-9]{3}([-. ])[0-9]{3}/1[0-9]{4}','***'),
'[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')

一三五、用内置的视点转化数据
select * from ( select rownum, flag, to_number(num) num from subtest where flag in ('A',

'C') ) where num > 0

一三六、测试存在的一组值
select student_id,test_id,grade_id,period_id,test_date,decode( grp_p_f,1,lpad('+',6),lpad

('-',6) ) metreq,decode( grp_p_f,1,0,decode( test_date,last_test,1,0 ) ) in_progress from (
select V.*,max(pass_fail)over(partition by student_id,grade_id,period_id) grp_p_f,max

(test_date)over(partition by student_id,grade_id,period_id) last_test from V) x