group by與partition by用法

--本文採用Oracle資料庫測試，前4個查詢為一組，後2個查詢為一組，每組前面的查詢是為了推出最後的查詢

--建立表，為了簡化處理，欄位類型都採用varchar
create table tb_sc(
  uName varchar2(10),
  uCourse varchar2(10),
  uScore varchar2(10)
);

--插入資料
insert into tb_sc values('張三','語文','80');
insert into tb_sc values('張三','數學','95');
insert into tb_sc values('李四','語文','90');
insert into tb_sc values('李四','數學','70');
insert into tb_sc values('王五','語文','90');
insert into tb_sc values('王五','數學','90');

 

--查詢所有結果如下

select * from tb_sc;

 

--①查詢每名學生的平均分（展示姓名、平均分）
select uName,avg(uScore) from tb_sc group by uName;

--②查詢每名學生的平均分並按降序排列（展示姓名、平均分）
select uName,avg(uScore) from tb_sc group by uName order by avg(uScore) desc;

--③查詢平均分數高於85分的學生（展示姓名、平均分）
select uName,avg(uScore) from tb_sc  group by uname having avg(uScore)>'85'  order by avg(uscore) desc;

--④查詢姓名不為張三且平均分數高於85分的學生（展示姓名、平均分）
select uName,avg(uScore) from tb_sc where uName!='張三' group by uname having avg(uScore)>'85'  order by avg(uscore) desc;

--⑤查詢所有學生的資訊並將每個學生的各科成績降序排列
select t.*,row_number() over(partition by t.uname order by t.uscore desc) rm from tb_sc t;

 

--⑥查詢每個學生考得最好的科目並展示該科目的成績
select * from (
select t.*,row_number() over(partition by t.uname order by t.uscore desc) rm from tb_sc t
) where rm=1;

 

註："row_number() over(partition by 分組欄位 order by 排序欄位)"開窗函數通常用於查詢所有分組並將各個表單進行排序