一、函數進階復習
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);
附（連續問題的解題思路）：
好文链接
您阅读本篇文章共花了：
发表评论