SQL笔记-2024/01/31

cross join
两个表的笛卡尔积
例如：
select s.name student_name,s.age student_age,s.class_id class_id,c.name class_name
from student s
cross join class c;

子查询
select s.name name,s.score score,s.class_id class_id
from student s
where s.class_id in (select distinct c.id from class c);

子查询 - exists
select s.name,s.age,s.class_id
from student s
where 1=1
and not exists (select 1 from class c where c.id = s.class_id);

组合查询
union 不保留重复行
union all 保留重复行
select s.name,s.age,s.score,s.class_id
from student s
union all
select sn.name,sn.age,sn.score,sn.class_id
from student_new sn;

开窗函数 - sum over
select s.id id,s.name name,s.age age,s.score score,s.class_id class_id,AVG(score) over (partition by class_id) as class_avg_score
from student s;

开窗函数 - sum over order by
select s.id id,s.name name,s.age age,s.score score,s.class_id class_id,SUM(score) over (partition by class_id order by score asc) as class_sum_score
from student s;

开窗函数 - rank
select s.id id,s.name name,s.age age,s.score score,s.class_id class_id,RANK() over (partition by class_id order by score desc) as ranking
from student s;

查询进阶 - 开窗函数 - row_number
select s.id id,s.name name,s.age age,s.score score,s.class_id class_id,ROW_NUMBER() over (partition by class_id order by score desc) as row_number
from student s;

开窗函数 - lag / lead
select s.id id,s.name name,s.age age,s.score score,s.class_id class_id,
LAG(name,1,null) over (partition by class_id order by score desc) as prev_name,
LEAD(name,1,null) over (partition by class_id order by score desc) as next_name
from student s;