【MySQL】MySQL进阶教程学习笔记2

自连接和非等值连接的应用

用于删除重复行

delete from Products P1
where P1.rowid < (select MAX(P2.rowid) from Products P2
							where P2.name = P1.name
							 and P2.price = P1.price) ;

查找局部不一致的列

• 查找图中家庭ID一致，但是住址不同的行

select DISTINCT A1.name,A1.address 
from Addresses A1,Addresses A2
wherer A1.family_id = A2.family_id
and A1.Address <> A2.Address;

排序

根据价格从高到低的顺序，对上表中的商品进行排序。我们让价格相同的商品位次也一样，而紧接着他们的商品则有两种排序方法，一种是跳过之后的位次，另一种是不跳过之后的位次

使用窗口函数

select name,price ,RANK(）OVER (ORDER BY price DESC)  as rank_1,
DENSE_RANK() OVER (ORDER BY price DESC) as rank_2
from Products;

• 使用非等值自连接实现

select P1.name,P1.price,
(select count(P2.price) from Products P2
where P2.price > P1.price)  + 1 as rank_1
from Products P1
order by rank_1;

select P1.name,P1.price,
(select count(DISTINCT(P2.price)) from Products P2
where P2.price > P1.price)  + 1 as rank_1
from Products P1
order by rank_1;

• SQL中有ALL和ANY两个限定谓词。因为ANY和IN是等价的，所以并不经常使用ANY。
• ALL可以和比较谓词一起使用，勇于表达“与所有的XX都相等”的，或者‘’比所有的XX都大”的意思。

HAVING子句的力量

Limit & Offset

• 当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量
• 当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
• 当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量

select * from test limit 10;  -- 取前十条数据
select * from test limit 1,10 -- 从第二条数据开始，取后十条数据
select * from test limit 1 offset 1 -- 从第二条数据开始，取一条数据

窗口函数

MySQL从8.0开始支持窗口函数

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

 

聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。

聚合函数也可以用于窗口函数中```

• 窗口函数用法:
• 函数名（[expr]） over子句

partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。

 

order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的订单金额排序来生成序号。

 

frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口：

references

• lead () 偏移函数

用法：
编编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

select distinct(l0) as ConsecutiveNums 
from (
			select Num as l0,lead(Num,1) over() as l1,lead(Num,2) over() as l2 
			from Logs )
			as c
			where c.l0 = c.l1 and c.l1 = c.l2;