SQL Cookbook 系列 - 若干另类目标

1. 使用SQL server的pivot运算符创建交叉报表
2. 使用SQLserver的unpivot运算符反转置交叉表报表
3. 使用oracle的model子句转换结果集
4. 从不固定位置提取字符串的元素
5. 求一年包含的天数（oracle的另一种解决方案）
6. 搜索字母数字混合的字符串
7. 使用oracle把整数转换为二进制数
8. 转置已分等级的结果集
9. 给两次转置的结果集增加列头
10. 在oracle中把标量子查询转化为复合子查询
11. 把连续数据分解为行
12. 计算相对于总数的百分比
13. 从oracle创建csv格式输出
14. 找到与模式不匹配的文本（oracle）
15. 使用内联视图转换数据
16. 测试一个组内是否存在某个值

 

1.使用SQL server的pivot运算符创建交叉报表

sqlserver:

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;

Note:这是sqlserver独有的运算符，第一次遇到，从来没用过

 

2.使用SQLserver的unpivot运算符反转置交叉表报表

Note:补充一个概念：胖表和瘦表。胖表是一张表中包含超过30个字段，这张表叫胖表。

一张表中包含的字段数少于10个，称这张表为瘦表。还有一种说法，包含转置的结果集也被称为胖表。

sqlserver:

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;

Note:这个是基于上一个案例的逆操作，没用过

 

3.使用oracle的model子句转换结果集

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

)

);

Note：这个属于oracle特有的操作，没用过

 

4.从不固定位置提取字符串的元素

oracle:

select substr(msg,

instr(msg,'[',1,1)+1,

instr(msg,']',1,1)-instr(msg,'[',1,1)-1)

val

from v;

Note:从指定的[]中提取内部值，多个[]也可以

 

5.求一年包含的天数（oracle的另一种解决方案）

oracle:

select 'Days in 2018:'||to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD')

as report

from dual;

Note:这个是获取当前年的天数，当然也可以做额外处理

 

6.搜索字母数字混合的字符串

select strings,translate(strings,

'abcdefghijklmnopqrstuvwxyz1234567890',

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

) translated

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

Note:将数字和字符分别替换成不同值，然后再进行观察

 

7.使用oracle把整数转换为二进制数

oracle:

select ename,sal,(

select bin from dual model by (0 attr)

measures(sal num,cast(null as vatchr2(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;

Note:这种操作我是第一次见到，这种转换是转换为16进制

 

8.转置已分等级的结果集

oracle:

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 grp order by sal desc,ename) grp_rnk

from (

select ebnane,sal,dense_rank()over(order by sal desc) rnk

from emp

) x

) y group by grp_rnk;

Note:对某种排序的结果集分等级操作，分为前三、中间三、其余的

 

9.给两次转置的结果集增加列头

oracle:

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 form 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

)

) group by flag;

Note:看上去很复杂，但是拆开发现只是同时对两张没有关联的表进行操作

 

10.在oracle中把标量子查询转化为复合子查询

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

select e.deptno,e.ename,e.sal,(

select generic_obj(d.dname,d.loc) from dept d

where e.deptno=d.deptno

) multival

from emp e

);

Note：将标量查询的结果集设置为一个对象，这样做很有特色

 

11.把连续数据分解为行

oracle:

with cartesian as (

select level id from dual connect by level<=100

)

select max(decode(id,1,sunstr(strings,p1+1,p2-1))) val1,

max(decode(id,2,sunstr(strings,p1+1,p2-1))) val2,

max(decode(id,3,sunstr(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;

Note:对一列具有以:作为值分隔符的数据来说，格式化还是很有必要的

 

12.计算相对于总数的百分比

oracle:

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;

Note：计算每一行占总结果的百分比，当然也可以做成小计的百分比

 

13.从oracle创建csv格式输出

oracle:

select deptno,list from (

select * from (

select deptno,empno,ename,

lag(deptno)over(partition by deptno order by empno) prior_deptno

from emp

) model demension 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;

Note：使用的非常少，因为csv文件使用频度不高，这种操作使用也很少见

 

14.找到与模式不匹配的文本（oracle）

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}');

Note：数据库是支持模式匹配的，这个要划重点

 

15.使用内联视图转换数据

oracle:

select * from (

select rownum,flag,to_number(num) num

from subtest where flag in ('A','C')

) where num>0;

Note：这个是常用的小技巧

 

16.测试一个组内是否存在某个值

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_date,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_date

from v

);

Note：统计两次成绩的问题，新增列是不通过的次数和最后的测试日期