今天这篇是我们数据库语法的最后一章，本章补充一些之前章节没有说到的知识点
1、行转列的结果集
我们在数据库语法总结（12）——报表的制作和整形-CSDN博客 的第1节中也提到了这项功能，不过我们现在要用一种新的函数来实现，pivot运算生成符语法为：pivot(聚合函数(展示字段) for 表头字段 in (可能出现的值)
select [1] as classno_1,
[2] as classno_2,
[3] as classno_3
from (select classno,c_num from tb_class) c
pivot(
count(c.c_num)
for c.classno in ([1],[2],[3])
)as classpivot2、列转行的结果集
有行转列，那我们就会想到列转行，同样的有运算符unpivot，语法为：
SELECT 列名称,需定义的列1名称,需定义的列2名称 from 表名称 unpivot (需定义的列2名称 for 需定义的列1名称 in(列2值1,列2值2,列2值3))
select c_num,classno
from(select classno_1,
classno_2,
classno_3
from tb_classpivot c
unpivot(
c.c_num
for c.classno in ('classno_1','classno_2','classno_3')
)as classunpivot
)大概是这个意思，可能语法会有一些问题，我没有在数据库实际操作，想要深入了解也可以参考Oracle使用pivot和unpivot函数实现行列转换_oracle_脚本之家
3、 行列转换
关于行列转换其实还有很多的方法可以实现，像我们之前提到的
行转列：max和decode、pivot、case when和group by 等等
列转行：union all、unpivot、cillection(需要创建集合)、model(部分数据库后期提供的高级函数)
具体格式呢我们在这就不过多的解释了，我们对应掌握一两种就可以了，下面这位博主的文章介绍的很详细，大家可以参考：oracle中行列转换总结_oracle 行转列-CSDN博客
4、 查找当前年份的天数
我们之前 数据库语法总结（9）——操作日期 中的第2节已经讲述过这个问题，我们在这列举一下其他的方式，先获取当前年份最后一天，然后转成3位的字符串天数模式就可以得到当年天数
SELECT to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD') AS days
FROM dual5、在Oracle中将整数转换为二进制形式
比如我们想要将整数2转换二进制就是10。本质是利用上面第3节中的高级函数model
下面这篇文章第3节介绍的很详细，大家可以参考，我们在此转载引用一下
ENAME SAL SAL_BINARY
---------- ----- --------------------
SMITH 800 1100100000
ALLEN 1600 11001000000
WARD 1250 10011100010
JONES 2975 101110011111
MARTIN 1250 10011100010
BLAKE 2850 101100100010
CLARK 2450 100110010010
SCOTT 3000 101110111000
KING 5000 1001110001000
TURNER 1500 10111011100
ADAMS 1100 10001001100
JAMES 950 1110110110
FORD 3000 101110111000
MILLER 1300 10100010100select 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
6、将排序后的等级数据进行分组转列
例如我们想获取表中的排名，然后把相同排名的放在同一列中展示。
思路肯定是先用开窗函数dense_rank() over() 进行排序，然后再用上面行转列的其中一个方法转置
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x
group by rn可以参考一下文章SQL 变换结果集成多行|极客教程
或者以下文章，按照固定第一列3个数据，第二列3个数据，剩下数据在第三列的方式，原理大概相同（参考第8节）SQL Cookbook 系列 - 若干另类目标_d10[any]=case when deptno[cv()]=10 then d10[cv()] -CSDN博客
7、给不同的两个结果集分别行转列成一个结果集后添加标题
我们直接引用以下博主的例子啊，大家掌握理解即可选读SQL经典实例笔记15_窗口函数-CSDN博客
数据表的内容：
select * from it_research
DEPTNO ENAME
------ --------------------
100 HOPKINS
100 JONES
100 TONEY
200 MORALES
200 P.WHITAKER
200 MARCIANO
200 ROBINSON
300 LACY
300 WRIGHT
300 J.TAYLOR
select * from it_apps
DEPTNO ENAME
------ -----------------
400 CORRALES
400 MAYWEATHER
400 CASTILLO
400 MARQUEZ
400 MOSLEY
500 GATTI
500 CALZAGHE
600 LAMOTTA
600 HAGLER
600 HEARNS
600 FRAZIER
700 GUINN
700 JUDAH
700 MARGARITO
想要的结果：
RESEARCH APPS
-------------------- ---------------
100 400
JONES MAYWEATHER
TONEY CASTILLO
HOPKINS MARQUEZ
200 MOSLEY
P.WHITAKER CORRALES
MARCIANO 500
ROBINSON CALZAGHE
MORALES GATTI
300 600
WRIGHT HAGLER
J.TAYLOR HEARNS
LACY FRAZIER
LAMOTTA
700
JUDAH
MARGARITO
GUINN
思路：使用笛卡尔积变成想要的行数，再用max和decode 进行转置
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
8、Oracle中将标量子查询转换为复合子查询
什么叫标量子查询，就是只能返回一个值，例如，进行如下的语句就会报错 ：
select e.deptno,
e.ename,
e.sal,
(select d.dname,d.loc,sysdate today
from dept d
where e.deptno=d.deptno)
from emp e
当然，一般情况下，我们可以把 EMP 表和 DEPT 表连接起来，进行查询即可。在此我们引入一种新的方式，复合子查询：将标量查询的结果集设置为一个对象
--首先添加一个新的对象类型 generic_obj
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
9、将一列数据按照分割字符转置为行
我们有如下数据：
STRINGS
-----------------------------------
entry:stewiegriffin:lois:brian:
entry:moe::sizlack:
entry:petergriffin:meg:chris:
entry:willie:
entry:quagmire:mayorwest:cleveland:
entry:::flanders:
Entry:robo:tchi:ken:
想要的结果为：
VAL1 VAL2 VAL3
--------------- --------------- ---------------
moe sizlack
petergriffin meg chris
quagmire mayorwest cleveland
robo tchi ken
stewiegriffin lois brian
willie
flanders
实现方式：
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
10、计算占总百分比
我们在 数据库语法总结（7）——数字处理_db2求中位数-CSDN博客 中提到过这一部分内容，当然如果是Oracle数据库，我们还可以使用内置函数ratio_to_report来实现
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_empsSQL Cookbook 系列 - 若干另类目标_d10[any]=case when deptno[cv()]=10 then d10[cv()] -CSDN博客
11、查询某一组数据中是否包含某一个值
例如，查询学生在一个学期内参加考试的次数及其通过的次数
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
下面这篇文章介绍的很详细，我们可以具体参考
下面呢，我查看网上有很多博客上大家分享的一张SQL图解析，放在这供大家参考
好了，到这篇暂时就是我们的结尾总结了，日后如果有的更深入的数据库方面的了解，会继续来补充。接下来的时间可能会比较紧张，所以文章可能有不好的地方大家多多包含
扫一扫
9043
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
