MySQL 基础篇

MySQL 基础篇

文章目录

• 一、简介
• 二、正则匹配
• 三、多表查询
• 四、聚合查询
• • 4.1 COUNT
  • 4.2 GROUP BY
  • 4.3 HAVING
  • 4.4 查询执行顺序
• 五、子查询
• • 5.1 单行子查询
  • 5.2 多行子查询
  • 5.3 相关子查询
• 六、窗口函数
• • 6.1 序号函数
  • 6.2 分布函数
  • 6.3 前后函数
  • 6.4 首尾函数
  • 6.5 其它函数

一、简介

• 查询规范

1. 每条命令以 ; 或 g 或 G 结束
2. 字符串型和日期时间类型的数据建议使用单引号 ' ' 表示，列的别名建议使用双引号 " " 而且不建议省略 AS

• 注释规范

1. 单行注释：# 注释文字(MySQL特有的方式)
2. 单行注释：-- 注释文字(--后面必须包含一个空格。)
3. 多行注释：/* 注释文字 */

二、正则匹配

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。

Name	Description
REGEXP	Whether string matches regular expression
NOT REGEXP	Negation of REGEXP
REGEXP_INSTR()	Starting index of substring matching regular expression
REGEXP_REPLACE()	Replace substrings matching regular expression
REGEXP_SUBSTR()	Return substring matching regular expression

三、多表查询

• JOIN

• UNION

1. UNION 操作符返回两个查询的结果集的并集，去除重复记录。
   

2. UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
   

• 7 中 Join 的实现

1. 中图：内连接 A ∩ B

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

2. 左上图：左外连接

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

3. 右上图：右外连接

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

4. 左中图：A - A ∩ B

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

5. 右中图：B - A ∩ B

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

6. 左下图：满外连接（左中图 + 右上图 A ∪ B）

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

7. 右下图：左中图 + 右中图 A ∪B - A ∩ B 或者 (A - A ∩ B) ∪ (B - A ∩ B)

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

• NATURAL JOIN

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

自动查询两张连接表中 所有相同的字段 ，然后进行等值连接 。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

• USING

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

使用 USING 指定数据表里的 同名字段 进行等值连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

四、聚合查询

4.1 COUNT

count(*)，count(1) 直接读行数会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

4.2 GROUP BY

在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

mysql> SELECT department_id, AVG(salary)-> FROM employees-> GROUP BY department_id ;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|          NULL |  7000.000000 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            30 |  4150.000000 |
|            40 |  6500.000000 |
|            50 |  3475.555556 |
|            60 |  5760.000000 |
|            70 | 10000.000000 |
|            80 |  8955.882353 |
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
+---------------+--------------+
12 rows in set (0.00 sec)mysql> 

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

mysql> SELECT department_id,COUNT(*)-> FROM employees-> WHERE department_id > 80-> GROUP BY department_id WITH ROLLUP;
+---------------+----------+
| department_id | COUNT(*) |
+---------------+----------+
|            90 |        3 |
|           100 |        6 |
|           110 |        2 |
|          NULL |       11 |
+---------------+----------+
4 rows in set (0.00 sec)mysql> SELECT department_id,SUM(salary)-> FROM employees-> WHERE department_id > 80-> GROUP BY department_id WITH ROLLUP;
+---------------+-------------+
| department_id | SUM(salary) |
+---------------+-------------+
|            90 |    58000.00 |
|           100 |    51600.00 |
|           110 |    20300.00 |
|          NULL |   129900.00 |
+---------------+-------------+
4 rows in set (0.00 sec)mysql> SELECT department_id,AVG(salary)-> FROM employees-> WHERE department_id > 80-> GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL | 11809.090909 |
+---------------+--------------+
4 rows in set (0.00 sec)mysql> 

4.3 HAVING

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

mysql> SELECT department_id, MAX(salary)-> FROM employees-> GROUP BY department_id-> HAVING MAX(salary)>10000 ;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.00 sec)mysql> 

• WHERE和HAVING的对比

1. WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；
2. HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
3. 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

4.4 查询执行顺序

• 书写顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

• 执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team am_id = am_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP am_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

五、子查询

• 引出

谁的工资比 Abel 高？

1. 先查询 Abel 的工资，再据此进行查询。

mysql> SELECT salary-> FROM employees-> WHERE last_name = 'Abel';
+----------+
| salary   |
+----------+
| 11000.00 |
+----------+
1 row in set (0.00 sec)mysql> SELECT last_name,salary-> FROM employees-> WHERE salary > 11000;
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| King      | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
...
+-----------+----------+
10 rows in set (0.00 sec)mysql> 

2. 子查询

mysql> SELECT last_name,salary-> FROM employees-> WHERE salary > (->   SELECT salary->   FROM employees->   WHERE last_name = 'Abel'-> );
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| King      | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
...
+-----------+----------+
10 rows in set (0.00 sec)mysql> 

5.1 单行子查询

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

• HAVING 中的子查询

查询最低工资大于50号部门最低工资的部门id和其最低工资

mysql> SELECT department_id, MIN(salary)-> FROM employees-> GROUP BY department_id-> HAVING MIN(salary) > (->   SELECT MIN(salary)->   FROM employees->   WHERE department_id = 50-> );
+---------------+-------------+
| department_id | MIN(salary) |
+---------------+-------------+
|          NULL |     7000.00 |
|            10 |     4400.00 |
|            20 |     6000.00 |
...
+---------------+-------------+
11 rows in set (0.02 sec)mysql> 

• CASE中的子查询

显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

mysql> SELECT employee_id, last_name,->   (CASE department_id->   WHEN->     (SELECT department_id FROM departments->     WHERE location_id = 1800)->     THEN 'Canada' ELSE 'USA' END) location-> FROM employees;
+-------------+-------------+----------+
| employee_id | last_name   | location |
+-------------+-------------+----------+
...
|         201 | Hartstein   | Canada   |
|         202 | Fay         | Canada   |
|         203 | Mavris      | USA      |
|         204 | Baer        | USA      |
...
+-------------+-------------+----------+
107 rows in set (0.00 sec)mysql> 

• 子查询中的空值问题

mysql> SELECT job_id-> FROM employees-> WHERE last_name = 'Haas';
Empty set (0.00 sec)mysql> SELECT last_name, job_id-> FROM employees-> WHERE job_id =->   (SELECT job_id->   FROM employees->   WHERE last_name = 'Haas');
Empty set (0.00 sec)mysql> 

• 非法使用子查询

mysql> SELECT MIN(salary)-> FROM employees-> GROUP BY department_id;
+-------------+
| MIN(salary) |
+-------------+
|     7000.00 |
|     4400.00 |
|     6000.00 |
...
+-------------+
12 rows in set (0.00 sec)mysql> SELECT employee_id, last_name-> FROM employees-> WHERE salary =->   (SELECT MIN(salary)->   FROM employees->   GROUP BY department_id);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> 

5.2 多行子查询

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

• IN

查询与141号或174号员工的 manager_id 和 department_id 相同的其他员工的 employee_id，manager_id，department_id

mysql> SELECT employee_id, manager_id, department_id FROM employees WHERE employee_id IN (141,174);
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
|         141 |        124 |            50 |
|         174 |        149 |            80 |
+-------------+------------+---------------+
2 rows in set (0.00 sec)mysql> SELECT employee_id, manager_id, department_id-> FROM employees-> WHERE (manager_id, department_id) IN (->   SELECT manager_id, department_id->   FROM employees->   WHERE employee_id IN (141,174))-> AND employee_id NOT IN (141,174);
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
...
|         198 |        124 |            50 |
|         199 |        124 |            50 |
|         175 |        149 |            80 |
...
+-------------+------------+---------------+
11 rows in set (0.01 sec)mysql>

• ALL

查询平均工资最低的部门id

mysql> SELECT department_id-> FROM employees-> GROUP BY department_id-> HAVING AVG(salary) <= ALL (->   SELECT AVG(salary) avg_sal->   FROM employees->   GROUP BY department_id-> );
+---------------+
| department_id |
+---------------+
|            50 |
+---------------+
1 row in set (0.00 sec)mysql> 

• ANY

返回其它job_id中比job_id为‘IT_PROG’部门任一工资（用 MAX(salary) 最高也行）低的员工的员工号、姓名、job_id 以及salary

mysql> SELECT employee_id, last_name, job_id, salary-> FROM employees-> WHERE salary < ANY (->   SELECT salary->   FROM employees->   WHERE job_id = 'IT_PROG'-> )-> AND job_id <> 'IT_PROG';
+-------------+-------------+------------+---------+
| employee_id | last_name   | job_id     | salary  |
+-------------+-------------+------------+---------+
|         110 | Chen        | FI_ACCOUNT | 8200.00 |
|         111 | Sciarra     | FI_ACCOUNT | 7700.00 |
|         112 | Urman       | FI_ACCOUNT | 7800.00 |
...
+-------------+-------------+------------+---------+
76 rows in set (0.00 sec)mysql> 

5.3 相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

• 示例

若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于2，输出这些相同 id 的员工的 employee_id,last_name 和其 job_id

mysql> ployee_id, last_name,e.job_id-> FROM employees e-> WHERE 2 <= (->   SELECT COUNT(*)->   FROM job_history->   WHERE employee_id = e.employee_id);
+-------------+-----------+---------+
| employee_id | last_name | job_id  |
+-------------+-----------+---------+
|         101 | Kochhar   | AD_VP   |
|         176 | Taylor    | SA_REP  |
|         200 | Whalen    | AD_ASST |
+-------------+-----------+---------+
3 rows in set (0.00 sec)mysql> 

• 在 FROM 中使用子查询

查询员工中工资大于本部门平均工资的员工的 last_name，salary 和其 department_id

mysql> SELECT last_name,salary,e1.department_id-> FROM employees e1,(->   SELECT department_id,AVG(salary) dept_avg_sal ->   FROM employees GROUP->   BY department_id) e2-> WHERE e1.`department_id` = e2.department_id-> AND e2.dept_avg_sal < e1.`salary`;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Hartstein | 13000.00 |            20 |
| Raphaely  | 11000.00 |            30 |
| Weiss     |  8000.00 |            50 |
...
+-----------+----------+---------------+
38 rows in set (0.01 sec)mysql> 

• 在ORDER BY 中使用子查询

查询员工的 id，salary 按照 department_name 排序

mysql> SELECT employee_id,salary-> FROM employees e-> ORDER BY (->   SELECT department_name->   FROM departments d->   WHERE e.`department_id` = d.`department_id`-> );
+-------------+----------+
| employee_id | salary   |
+-------------+----------+
|         178 |  7000.00 |
|         205 | 12000.00 |
|         206 |  8300.00 |
...
+-------------+----------+
107 rows in set (0.02 sec)mysql> 

• EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

# 查询公司管理者的employee_id，last_name，job_id，department_id信息mysql> SELECT employee_id, last_name, job_id, department_id-> FROM employees e1-> WHERE EXISTS (->   SELECT *->   FROM employees e2->   WHERE e2.manager_id = e1.employee_id);
+-------------+-----------+---------+---------------+
| employee_id | last_name | job_id  | department_id |
+-------------+-----------+---------+---------------+
|         100 | King      | AD_PRES |            90 |
|         101 | Kochhar   | AD_VP   |            90 |
|         102 | De Haan   | AD_VP   |            90 |
...
+-------------+-----------+---------+---------------+
18 rows in set (0.00 sec)mysql> 

• 相关更新

在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));# 2）
UPDATE employees e
SET department_name = (SELECT department_nameFROM departments dWHERE e.department_id = d.department_id);

• 相关删除

DELETE FROM table1 alias1
WHERE column operator (SELECT expressionFROM table2 lumn = lumn);

六、窗口函数

官网：.0/en/window-functions.html

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

函数分类	函数	函数说明
序号函数	ROW_NUMBER()	顺序排序
	RANK()	并列排序，会跳过重复的序号，比如序号为 1、1、3
	DENSE_RANK()	并列排序，不会跳过重复的序号，比如序号为 1、1、2
分布函数	PERCENT_RANK()	等级值百分比
	CUME_DIST()	累积分布值
前后函数	LAG(expr, n)	返回当前行的前 n 行的 expr 的值
	LEAD(expr, n)	返回当前行的后 n 行的 expr 的值
首尾函数	FIRST_VALUE(expr)	返回第一个 expr 的值
	LAST_VALUE(expr)	返回最后一个 expr 的值
其它函数	NTH_VALUE(expr, n)	返回第 n 个 expr 的值
	NTILE(n)	将分区中的有序数据分为 n 个桶，记录桶编号

• 数据准备

CREATE TABLE goods(id INT PRIMARY KEY AUTO_INCREMENT,category_id INT,category VARCHAR(15),NAME VARCHAR(30),price DECIMAL(10,2),stock INT,upper_time DATETIME
);INSERT INTO goods(category_id,category,NAME,price,stock,upper_time) VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

6.1 序号函数

• ROW_NUMBER()

ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

# 查询 goods 数据表中每个商品分类下价格最高的3种商品信息。
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) ASrow_num, id, category_id, category, NAME, price, stockFROM goods) t
WHERE row_num <= 3;

• RANK()

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

# 使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stock
FROM goods;

• DENSE_RANK()

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

# 使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) ASrow_num, id, category_id, category, NAME, price, stock
FROM goods;

6.2 分布函数

• PERCENT_RANK()

等级值百分比函数 PERCENT_RANK() = (rank - 1) / (rows - 1)，其中 rank 的值为使用 RANK() 函数产生的序号，rows 的值为当前窗口的总记录数。

# 举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。SELECTRANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;# 或SELECTRANK() OVER w AS r,PERCENT_RANK() OVER w AS pr,id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

• CUME_DIST()：查询小于或等于某个值的比例。

# 举例：查询goods数据表中小于或等于当前价格的比例。SELECTRANK() OVER w AS r,CUME_DIST() OVER w AS cd,id, category_id, category, NAME, price, stock
FROM goodsWINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

6.3 前后函数

• LAG(expr, n)：返回当前行的前n行的expr的值

# 举例：查询goods数据表中前一个商品价格与当前商品价格的差值。SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_priceFROM goodsWINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;

• LEAD(expr,n)：返回当前行的后n行的expr的值

# 举例：查询goods数据表中后一个商品价格与当前商品价格的差值。SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_price
FROM (SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;

6.4 首尾函数

• FIRST_VALUE(expr)：返回 expr 的第一个值

# 举例：按照价格排序，查询goods数据表中当前商品与第一件商品价格的差值。SELECT id, category, NAME, price, first_price, price - first_price AS diff_price
FROM (SELECT id, category, NAME, price,FIRST_VALUE(price) OVER w AS first_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;

• LAST_VALUE(expr)：返回 expr 的最后一个值

# 举例：按照价格排序，查询goods数据表中当前商品与最后一件商品价格的差值。/*** 默认帧规范如下：*   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW * 这意味着框架从第一行开始，到结果集的当前行结束。* * 因此，为了获得加班时间最长的员工，我们将框架规格更改为以下内容：*   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING */SELECT category_id, category, NAME, price, last_price, last_price - price  AS diff_price
FROM (SELECT category_id, category, NAME, price, LAST_VALUE(price) OVER w AS last_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY priceRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) t;

6.5 其它函数

• NTH_VALUE(expr,n)：返回 expr 的第 n 个值

# 举例：按照价格排序，查询goods数据表中当前商品与第三名商品的价格差值。SELECT category_id, NAME, price,price - third_price AS diff_price
FROM (SELECT category_id, NAME, price, NTH_VALUE(price,3) OVER w AS third_priceFROM goods WINDOW w AS (PARTITION BY category_idORDER BY price DESCRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) t;

• NTILE(n)：数将分区中的有序数据分为n个桶，记录桶编号

# 举例：按照价格排序，将goods表中的商品按照价格分为3组。SELECT NTILE(3) OVER w AS nt, id, category, NAME, price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);