一、函數進階復習
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
小红
数学
90
1
小李
物理
85
小红
英语
80
2
代碼：
通過row number序號查詢
select student_name,course_name,score, row_number() over (partition by student_name order by score desc ) 排名 from t_score;
2、 去除最大值、最小值后求平均值
“薪水表”中记录了雇员编号、部门编号和薪水。要求查询出每个部门去除最高、最低薪水后的平均薪水。
已知条件 薪资表
结果
employee_id
department_id
salary
department_id
avg_salary
1
1
50000
1
50000
2
1
52000
2
60000
3
1
48000
4
1
51000
5
1
49000
6
2
60000
7
2
58000
8
2
62000
9
2
59000
10
2
61000
代碼：
with t1 as ( select department_id,salary, row_number() over (partition by department_id order by salary )序号, row_number() over (partition by department_id order by salary desc )序号1 from t_salary_table ) select department_id,avg(salary) from t1 where 序号>1 and 序号1>1 group by department_id;
3、Top N问题
查询前三名的成绩
-- “成绩表”中记录了学生选修的课程号、学生的学号，以及对应课程的成绩。 -- 为了对学生成绩进行考核，现需要查询每门课程前三名学生的成绩。 -- # todo 注意：如果出现同样的成绩，则视为同一个名次
输入
输出
course_id
student_id
score
course_id
student_id
score
rn
1
1
85
1
3
92
1
1
2
78
1
6
92
1
1
3
92
1
8
92
1
1
4
90
1
4
90
2
1
5
80
1
1
85
3
1
6
92
1
9
85
3
1
7
78
2
3
90
1
1
8
92
2
8
90
1
1
9
85
2
1
88
2
2
1
88
2
6
88
2
2
2
82
2
4
85
3
2
3
90
2
4
85
2
5
78
2
6
88
2
7
82
2
8
90
2
9
82
代碼：
with t1 as ( select student_id,course_id,score, dense_rank() over (partition by COURSE_ID order by score desc )序号 from t_score2) select course_id,student_id, score from t1 where 序号 <=3;
4、Top N問題
查询排在前两名的工资
“雇员表”中是公司雇员的信息，每个雇员有其对应的工号、姓名、工资和部门编号。
现在要查找每个部门工资排在前两名的雇员信息，若雇员工资一样，则并列获取。
已知条件 雇员表
结果表
emp_id
emp_name
salary
department_id
emp_id
emp_name
salary
department_id
rn
1
小明
50000
1
6
小刚
62000
1
1
2
小红
52000
1
4
小张
60000
1
2
3
小李
48000
1
10
小华
52000
2
1
4
小张
60000
1
11
小雷
52000
2
1
5
小王
58000
1
9
小晓
49000
2
2
6
小刚
62000
1
7
小丽
45000
2
8
小芳
47000
2
9
小晓
49000
2
10
小华
52000
2
11
小雷
52000
2
代碼：
with t1 as ( select emp_id,emp_name,department_id,salary,dense_rank() over (partition by department_id order by salary desc )序号 from t_employee ) select emp_id,emp_name,department_id,salary from t1 where 序号<3;
5、連續問題
员工的 累计工资汇总 可以计算如下:
对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作，那么他们在前几个月的有效工资为 0 。
不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
返回按 id 升序排序 的结果表。如果 id 相等，请按 month 降序排序。
代碼：
select id,month,sum(salary) over (partition by id order by month desc range between current row and 2 following )馬内 from t_employee1;
6、连续空余座位抛真题
-- # todo 查找电影院所有连续可用的座位。 -- # todo 返回按 seat_id 升序排序 的结果表。 -- # todo 测试用例的生成使得两个以上的座位连续可用。
代碼1：
-- 第一種方法 -- 1、先按照free分組 打亂seat——id -- 2、再加入一條等差數列row num 進行對比 因爲使用over partition by 所以 可以直接加等差數列 select Cinema.*,row_number() over (partition by free order by seat_id) 等差數列 from CINEMA; -- 3、再把沒有空位置的Free給篩選掉 select Cinema.*,row_number() over (partition by free order by seat_id) 等差數列 from CINEMA where free=1; -- 4、桌位id減去等差數列如果數字一樣(差值)，則證明是連續的 select Cinema.*, seat_id-row_number() over (partition by free order by seat_id) 差值 from CINEMA where free=1; -- 5、按差值和判斷是否連續的字段（free）分組，進行計數 with t1 as ( select Cinema.*, seat_id-row_number() over (partition by free order by seat_id) 差值 from CINEMA where free=1) select t1.*,count(*) over (partition by t1.差值,free) 計數 from t1; -- 6、計數條件判斷是否大於XXX，幾個連續的，如果連續2就大於等於2，即可 with t1 as ( select Cinema.*, seat_id-row_number() over (partition by free order by seat_id) 差值 from CINEMA where free=1), t2 as ( select t1.*,count(*) over (partition by t1.差值,free) 計數 from t1) select t2.seat_id from t2 where 計數>=2 order by seat_id ;
代碼2：
-- 第二種方法 with t1 as ( select Cinema.*, lead(free, 1) over (order by seat_id) rn1, lag(free, 1) over (order by seat_id) rn2 from Cinema) select seat_id from t1 where (t1.free = 1 and t1.rn1 = 1) or (t1.rn1 is null and t1.rn2 = 1 and t1.free=1);
附（連續問題的解題思路）：