SQL高级语句部分知识点笔记

高级语句和前一篇中基础语句的区别在于，高级语句使用的场景不太相同，需要对基础语句进行组合性使用

窗口函数

标准语法： over partition by 字段名  order by 字段名 asc | desc

（注意 partition by 和 order by 二者之间没有 ，进行隔开，切忌加上 逗号）

语法讲解：over()中两个子句为可选项，partition by指定分区依据，order by 指定排序依据

窗口函数的作用：

理解起来好比，我首先在第一个网页中进行一些操作得到结果，然后再另开一个新的网页，对刚刚的数据进行处理得到一些结果，然后再将这个新开的网页中得到的结果 匹配回第一个网页中。

更加生动的例子就是：在计算数学题时，在试卷的空白处写好了解题过程，然后 在草稿纸中进行计算得到最终结果，最后再将结果 写到试卷中

在这个过程中，草稿纸的作用就是  窗口函数的作用

如何理解窗口函数的语法结构呢？为什么over()要放在函数后面？该如何理解？

答：以rank() over()为例，over后面的相当于是草稿，也即是窗口函数，在over()里面将数据进行分区和排序，就好比是在over()里面做草稿，然后再将草稿中的结果 返回。返回的方式就是 rank()。理解起来就像，over()将草稿打好，然后根据草稿的推理，直接将答案写到试卷上，而这个答案的获取 就是 rank()

 检测到窗口函数以后，返回时是返回到having语句之后，order by和limit可以引用窗口函数的结果，而having之前的语句则不行

排序窗口函数：

rank() over()：跳跃式排序。比如现在有成绩 100 100 99 98，那么排名为 1，1，3，4

dense_rank() over() ：并列连续型排序。有成绩 100 100 99 98，那么排名为 1，1，2，3

row_number() over()：连续型排序。有成绩 100 100 99 98，那么排名为 1，2，3，4

当数值没有重复时，三个函数作用相同

例题：

查询2017年选区为‘$14000024’的所有候选人所在团体和其选票数(votes)、还有候选人得票数在选区内对应的排名，结果按照团队party排序

select party, votes,
rank()over(order by votes desc)
from ge
where area = '$14000024' and yr = 2017

偏移分析函数：

lag(字段名， 偏移量 ( 默认值 )  ) over()

作用：将某个字段向后延迟多少行  lag这个英文单词本身的意思是：滞后; 落后于

所以说，这个函数就相当于是将 某个字段 在某行 延迟 多少个单位

而lead函数，就是向下偏移

lead(字段名，偏移量 ( 默认值) ) over()

例题：

已知有一个疫情的表格，有国家、日期、每日累计确诊病例、累计治愈、累计死亡。

现要求查询法国和德国1月每天新增确诊人数，最后显示国家名，标准日期(2020-01-27)，当天截止时间累计确诊人数，昨天截止时间累计确诊人数，每天新增确诊人数，按照截至时间排序。如何处理？

思路：

用今天的累计确诊  减去  昨天的累计确诊 便能够得到  今日新增确诊

那么如何用代码实现？

select country,date
confirmed 累计确诊,
lag(confirmed, 1) over(partition by country order by date asc) 昨日累计确诊,
confirmed - lag(confirmed, 1) over(partition by country, order by date asc) 新增确诊
from covid
where country in ('France','Germany') and month(date) = 1
order by date

例题：

查询意大利每周新增确诊数，最后显示国家名，标准日期，每周新增人数，按照截止时间排序

select country 国家名,
date_format(whn, '%Y-%m-%d') 标准日期,
confirmed - lag(confirmed.1)over(order by date) 每周新增人数
from covid
where country = 'Italy' and weekday(whn) = 0
order by whn

窗口函数注意事项：

1.窗口函数只能写在select子句中

2.窗口函数中的 partition by 子句可以指定数据的分区，作用上和group by基本一致，但是区别在于：partition by 并不会去重

3.窗口函数中没有 partition by 子句时，说明此时不对数据进行分区，所有的数据组成一个区

4.排序窗口函数中 order by 子句是必选项，窗口函数中 order by子句在分区内，一句指定字段和排序方法对数据进行排序

5.rank()  dense_rank()  row_number()  这三个排序窗口函数的异同之处。

主知识九：表连接

知识点引入：

一共有三种连接方法：内连接、左连接、右连接

左右连接其实就是在内连接的基础之上进行一个筛选

内连接语法：

select 字段名

from 表1 (inner) join 表2 on 表1.字段名 = 表2.字段名

注意：一般连接都是默认 内连接，所以 单纯写 join  就 默认是 inner join

逻辑是：表1中的字母逐个和表2中对应的字母连接，得到新的行。例如表1中的A 1，先和表2中的A全部连接完之后，再到表1中的A 2和表2中的A 逐个连接。

若出现像表1中F在表2中没有F对应的这种情况，那么结果便会得到空值 null

left join左连接：

左连接强调的是：左边表格里面的值 一个都不能少，右边的表格要与左边的表格相匹配。

如果左边的表格里，匹配的字段是空值，那么也会被剔除掉

right join右连接：

右连接强调的是：右边表格里面的值 一个都不能少，左边的表格要与右边的表格相匹配。

如果右边的表格里，匹配的字段是空值，那么也会被剔除掉

有如下表格：有三个表格 game、goal、eteam

game包含id、mdate(比赛日期)、stadium()、team1和team2；goal包含matchid、teamid、player、gtime(进球时间)；team包括id(队伍编号)、teamname(队名)、coach(教练)

例题：

查询有球员名叫Mario进球的队伍1和队伍2以及球员姓名

select team1, team2, player
from game join goal on game.id = goal.matchid
where player = 'Mario'

表连接时的一些细节处理：

在from语句中，也可以给表格起别名，方法跟select中起别名一样，目的在于方便表连接时能够简便些

例题：

查询每场比赛、每个球队的得分情况，并返回mdate team1 score1 team2 score2

主知识点十：子查询

作用理解：相当于是 先进行查询，得到一个查询结果，然后再将该查询结果作为条件 再进行查询

知识点汇总：

子查询是可以自己正常独立运行的语句，作用就在于：将子查询的结果（结果并不仅仅指一个数值，也可以是指一个表格）作为条件 或者是 from语句查询的表格  再进行查询

1.比如说，可以在where 条件的后面运用到 子查询

2.在from语句中运用子查询得到的 表 ，作为查询的表格来源

例题：

查询2017年所有在爱丁堡的选区当选议员 所在选区及其团队。已知爱丁堡选区编号为S14000021至S14000021，当选议员即各选取得票数最高的候选人。

select constituency, party, votes
from (
     子查询语句，返回一个表格
)  as rk
where rk.position = 1
但是有一点必须得注意！ from 子查询必须得给别名！而且，在后面选择相关字段时格式上也必须得发生改变！

这样处理的原因是：如果没有别名，那么数据库将无法识别，不知道查询的字段来自于哪个表格