Oracle進階SQLDay03

一、函數進階復習

1、行轉列

select '用水儿量（噸）' 统计项,
sum(case when t_account.month='01' then USENUM end) 一月,
sum(case when t_account.month='02' then USENUM end) 二月,
sum(case when t_account.month='03' then USENUM end) 三月,
sum(case when t_account.month='04' then USENUM end) 四月,
sum(case when t_account.month='05' then USENUM end) 五月,
sum(case when t_account.month='06' then USENUM end) 六月
from t_account
    union all
select '金額（元）' 统计项,
sum(case when t_account.month='01' then money end) 一月,
sum(case when t_account.month='02' then money end) 二月,
sum(case when t_account.month='03' then money end) 三月,
sum(case when t_account.month='04' then money end) 四月,
sum(case when t_account.month='05' then money end) 五月,
sum(case when t_account.month='06' then money end) 六月
from t_account;

2、nvl函數統計0值

统计用水量 ，收费金额 (分类型统计)

根据业主类型分别统计每种居民的用水量 (整数 ，四舍五入) 及收费金额 ，如

果该类型在台账表中无数据也需要列出值为 0 的记录.

效果如下 ：

分析 ：这里所用到的知识点包括左外连接、sum()、分组 group by  、round()  和 nvl()

select  distinct t2.name 姓名,
       round(sum(nvl(usenum,0)) over (partition by t2.id),0 )用水量,
       sum(nvl(money,0)) over (partition by t2.id) 總金額
from
    t_account t1  right join t_ownertype t2 on t1.ownertype=t2.id;

 3、簡單查詢

统计每个区域的业主户数 ，如果该区域没有业主户数也要列出 0

select distinct t3.name 區域,
count(t1.id) over (partition by t3.id) 人數
from t_owners t1
join t_address t2 on t1.addressid=t2.id
right join t_area t3 on t2.areaid=t3.id;

二、窗口函數進階

1、學生成績查詢

现有“成绩表”，需要我们取得每名学生不同课程的成绩排名.

已知条件 分数表				结果
student_name	course_name	score		student_name	course_name	score	rn
小明	数学	85		小明	物理	92	1
小明	英语	78		小明	数学	85	2
小明	物理	92		小明	英语	78	3
小红	数学	90		小李	数学	90	1
小红	英语	80		小李	英语	85	2
小李	数学	90		小李	物理	85	3
小李	数学	60		小李	数学	60	4
小李	英语	85