- 使用SQL server的pivot运算符创建交叉报表
- 使用SQLserver的unpivot运算符反转置交叉表报表
- 使用oracle的model子句转换结果集
- 从不固定位置提取字符串的元素
- 求一年包含的天数（oracle的另一种解决方案）
- 搜索字母数字混合的字符串
- 使用oracle把整数转换为二进制数
- 转置已分等级的结果集
- 给两次转置的结果集增加列头
- 在oracle中把标量子查询转化为复合子查询
- 把连续数据分解为行
- 计算相对于总数的百分比
- 从oracle创建csv格式输出
- 找到与模式不匹配的文本（oracle）
- 使用内联视图转换数据
- 测试一个组内是否存在某个值
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：统计两次成绩的问题，新增列是不通过的次数和最后的测试日期