标签：name employees 汇总查询 department MySQL DQL id SELECT

1 数据准备

1.1 建表语句

创建四个表：地址表、部门表、职位表和员工表：

CREATE TABLE `locations` (
  `location_id` INT NOT NULL AUTO_INCREMENT,
  `street_address` VARCHAR(40) DEFAULT NULL,
  `postal_code` VARCHAR(12) DEFAULT NULL,
  `city` VARCHAR(30) DEFAULT NULL,
  `state_province` VARCHAR(25) DEFAULT NULL,
  `country_id` VARCHAR(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=INNODB AUTO_INCREMENT=3206 DEFAULT CHARSET=gb2312;

CREATE TABLE `departments` (
  `department_id` INT NOT NULL AUTO_INCREMENT,
  `department_name` VARCHAR(3) DEFAULT NULL,
  `manager_id` INT DEFAULT NULL,
  `location_id` INT DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=INNODB AUTO_INCREMENT=274 DEFAULT CHARSET=gb2312;

CREATE TABLE `jobs` (
  `job_id` VARCHAR(10) NOT NULL,
  `job_title` VARCHAR(35) DEFAULT NULL,
  `min_salary` INT DEFAULT NULL,
  `max_salary` INT DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=INNODB DEFAULT CHARSET=gb2312;

CREATE TABLE `employees` (
  `employee_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(20) DEFAULT NULL,
  `last_name` VARCHAR(25) DEFAULT NULL,
  `email` VARCHAR(25) DEFAULT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `job_id` VARCHAR(10) DEFAULT NULL,
  `salary` DOUBLE(10,2) DEFAULT NULL,
  `commission_pct` DOUBLE(4,2) DEFAULT NULL,
  `manager_id` INT DEFAULT NULL,
  `department_id` INT DEFAULT NULL,
  `hiredate` DATETIME DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=INNODB AUTO_INCREMENT=212 DEFAULT CHARSET=gb2312;

1.2 ER图

2 基础查询

2.1 基础查询

SELECT last_name FROM employees;	# 查询一个字段
SELECT last_name,salary,email FROM employees;	# 查询多个字段
SELECT * FROM employees;	# 查询所有字段
SELECT 100;	# 查询常量值
SELECT 100%98;	# 查询表达式
SELECT version();	# 查询函数
SELECT CONCAT('a','b','c') AS 结果;	# 字符串拼接

2.2 条件查询

2.2.1 普通条件查询

条件运算符：
- 大于小于等于：>、<、=、>=、<=
- 等于：=
- 不等于：!=、<>
逻辑运算符：
- 且：&&、and（两个条件都为true，结果为true，反之为false）
- 或：||、or（只要有一个条件为true，结果为true，反之为false）
- 非：!、not（如果连接的条件本身为false，结果为true，反之为false）

# 案例1：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT  last_name, salary, commission_pct FROM employees 
WHERE salary >= 10000
AND salary <= 25000;    

# 案例2：查询部门编号不是在270到280之间，或者工资高于25000的员工信息
SELECT * FROM employees
WHERE NOT ( department_id >= 270 AND department_id <= 280 )
OR salary > 25000;

2.2.2 模糊查询

模糊查询：like 通配符：

%：匹配任意多个字符，包含0个字符
_：匹配任意单个字符

# 案例1：查询员工名中包含字符明的员工信息
SELECT * FROM employees 
WHERE last_name LIKE '%明%';
# 案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT  last_name, salary FROM employees 
WHERE last_name LIKE '__e_a%';
# 案例3：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees 
WHERE last_name LIKE '_$_%' ESCAPE '$'; # 停止转义

2.2.3 范围查询

between ... and ...：在某个范围之间，前后不可交换
in：指定范围
is null|is not null：空/非空

# 案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees 
WHERE employee_id BETWEEN 100 AND 120;

# 案例2：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees 
WHERE job_id IN ( 'IT_PROT', 'AD_VP', 'AD_PRES' );

# 案例3：查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees 
WHERE commission_pct IS NULL;

# 案例4：查询有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees 
WHERE commission_pct IS NOT NULL;

2.3 排序查询

关键字：order by

升序（默认）：ASC
降序：DESC

# 按单个字段排序
SELECT * FROM employees 
ORDER BY salary DESC;

# 案例1：查询部门编号>=90的员工信息，并按员工编号降序
SELECT * FROM employees 
WHERE department_id >= 90 
ORDER BY employee_id DESC;

# 案例2：查询员工信息 按年薪升序
SELECT *, salary * 12 *(1+ifnull ( commission_pct, 0 )) AS 年薪 
FROM employees ORDER BY	年薪 ASC;

# 案例3：查询员工名，并且按名字的长度降序
SELECT length( last_name ),	last_name 
FROM employees 
ORDER BY length( last_name ) DESC;

# 案例4：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT * FROM employees 
ORDER BY salary DESC, employee_id ASC;

2.4 分组查询

2.4.1 分组规则

select 查询列表 from 表 
【where 筛选条件】
group by 分组的字段
【having 筛选条件】通常会结合聚合函数
【order by 排序的字段】;

	操作的表	位置	连接的关键字
分组前筛选	原始表	group by之前	where
分组后筛选	group by后的结果子集	group by之后	having

2.4.2 分组前的筛选

# 案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX( salary ), department_id FROM employees 
WHERE email LIKE '%a%' 
GROUP BY department_id;

# 案例2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG( salary ), manager_id FROM employees
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id;

2.4.3 分组后的筛选

# 案例1：查询哪个部门的员工个数>5
SELECT COUNT(*), department_id FROM	employees 
GROUP BY department_id 
HAVING COUNT(*)> 5;

# 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, MAX( salary ) FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX( salary )> 12000;

# 案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id, MIN( salary ) FROM employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN( salary )> 5000;

2.5 分页查询

limit 偏移量，每页数量

SELECT DISTINCT 查询列表
FORM 表
【JOIN 表2】
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段
LIMIT【offset,】size;

2.6 联合查询

UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同：

对重复结果的处理：UNIO在进行表连接后会筛选掉重复的记录，Union All不会去除重复记录。
对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
从效率上说：UNION ALL要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

# 案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
(
	SELECT id,cname 
	FROM t_ca WHERE csex='男'
)
UNION ALL
(
	SELECT t_id,tname 
	FROM t_ua WHERE tGender='male';
)

3 函数查询

3.1 字符串操作函数

# length 获取参数值的字节个数
SELECT	length( 'john' );
	
# concat 拼接字符串
SELECT concat( last_name, '_', first_name ) 姓名 
FROM employees;
	
# upper/lower
SELECT upper( 'john' );
SELECT lower( 'joHn' );
SELECT concat(upper( last_name ), lower( first_name )) 姓名 
FROM employees;
	
# substr、substring、substring_index索引从1开始
SELECT substr( '阿珍最后还是爱上了阿强', 7 ) out_put;
SELECT substr( '阿珍最后还是爱上了阿强', 1, 3 ) out_put;
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( "阿珍-爱上了-阿强", "-", 2 ), '-',- 1 ) # 爱上了

# instr 返回子串第一次出现的索引，如果找不到返回0
SELECT instr( '阿珍最后还是爱上了阿强', '阿强' ) AS out_put;

# trim
SELECT length(trim( '    阿强    ' )) AS out_put;

SELECT trim( 'a' FROM 'aaaaaaaaa阿强aaaaaaaaaa' ) AS out_put;
	
# lpad 用指定的字符实现左填充指定长度
SELECT lpad( '阿珍', 12, '*' ) AS out_put;# **********阿珍

# rpad 用指定的字符实现右填充指定长度
SELECT rpad( '阿珍', 12, 'ab' ) AS out_put;
	
# replace 替换
SELECT REPLACE( '阿珍最后还是爱上了阿强', '阿强', '他' ) AS out_put;

3.2 数字函数

round：四舍五入
ceil：向上取整
floor：向下取整

# round 四舍五入
SELECT round(-1.55);
SELECT round(1.567,2);

# ceil 向上取整，返回>=该参数的最小整数
SELECT ceil(-1.02);

# floor 向下取整，返回<=该参数的最大整数
SELECT floor(-9.99);

# truncate 截断
SELECT truncate(1.69999,1);

# mod取余
SELECT mod(10,-3);
SELECT 10%3;

3.3 日期函数

获取时间函数：

now 返回当前系统日期+时间
curdate 返回当前系统日期，不包含时间
curtime 返回当前时间，不包含日期

获取时间中指定的部分，如年份、月份、日期、小时等等

year
month
monthname
day
hour

日期操作函数

str_to_date：字符串转日期
date_format：日期转指定格式的字符串
date_add、data_sub：日期加减操作

# str_to_date 将字符通过指定的格式转换成日期
SELECT str_to_date( '1998-3-2', '%Y-%c-%d' ) AS out_put;
	
# date_format 将日期转换成字符
SELECT date_format( NOW(), '%y年%m月%d日' ) AS out_put;
	
# date_add 对日期进行加减操作
SELECT date_add( NOW(), INTERVAL 1 DAY );
SELECT date_add( NOW(), INTERVAL -1 HOUR );

3.4 聚合函数

常用五种聚合函数：sum、avg、count、min、max

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

4 多表查询

4.1 92标准

4.1.1 等值连接

# 案例1：查询女神名和对应的男神名
SELECT beauty_name, boyName FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

# 案例2：查询有奖金的员工名、部门名（除了等值连接的判断还带有其他的判断）
SELECT last_name, department_name, commission_pct
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

# 案例3：查询每个城市的部门个数（带上分组）
SELECT COUNT(*) 个数, city FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

# 案例4：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name, d.`manager_id`, MIN(salary)
FROM departments d, employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name, d.`manager_id`;

# 案例5：查询每个工种的工种名和员工的个数，并且按员工个数降序（带上排序）
SELECT job_title, COUNT(*) 
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

# 案例6：查询员工名、部门名和所在的城市（n个表至少得有n-1个等值连接的判断条件）
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

4.1.2 非等值连接

SELECT salary, grade_level 
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

4.1.3 自连接

SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

4.2 99标准

99标准的表连接提供了多种方式：如内连接、左（右）外连接、全外连接等等，在实际应用中，内连接和左（右）外连接使用频率比较高

4.2.1 内连接

内连接分类：等值、非等值、自连接

可以添加排序、分组、筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集语法：

select 查询列表
from 表1 别名
(inner) join 表2 别名
on 连接条件;

等值连接

# 案例1：查询员工名、部门名
SELECT last_name, department_name 
FROM departments d JOIN employees e
ON e.`department_id` = d.`department_id`;

# 案例2：查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name, job_title
FROM employees e INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

# 案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）
SELECT city,COUNT(*) 部门个数
FROM departments d INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

# 案例4:查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*) 个数, department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

# 案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name, job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

非等值连接

# 案例1：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

# 案例2：查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*), grade_level
FROM employees e JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;

自连接

# 案例1：查询员工的名字、上级的名字
SELECT e.last_name, m.last_name
FROM employees e JOIN employees m
ON e.`manager_id` = m.`employee_id`;

# 案例2：查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name, m.last_name
FROM employees e JOIN employees m 
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

4.2.2 外连接

外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值。如果从表中没有和它匹配的，则显示null。外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
左外连接，left join左边的是主表；右外连接，right join右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

# 案例1：查询哪个部门没有员工(左外)
SELECT d.*,e.employee_id FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
 
# 案例2：查询哪个部门没有员工(右外)
SELECT d.*,e.employee_id FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

4.2.3 新特性

自然连接NATURAL JOIN：在对两张表进行等值连接时，如果连接的字段名相同，可以使用NATURAL JOIN进行简化，它会自动把两个表名字一样的字段进行等值连接。

# 查询员工的id，姓名和所在的部门
SELECT employee_id, last_name, department_name 
FROM employees e JOIN departments d 
ON e.`department_id` = d.`department_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 
ON e.`department_id` = d.`department_id`
等价于
SELECT employee_id,last_name,department_name 
FROM employees e 
JOIN departments d 
USING (department_id);

5 子查询

5.1 标量子查询

如果一个查询语句的结果是唯一的一个值，那么可以将其作为一个标量使用。在标量子查询中，一定要加括号！

# 案例1：谁的工资比Abel高?
SELECT * FROM employees
WHERE salary>(
	SELECT salary FROM employees WHERE last_name = 'Abel'
);

# 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary FROM employees
WHERE job_id = (
	SELECT job_id FROM employees WHERE employee_id = 141
) 
AND salary > (
	SELECT salary FROM employees WHERE employee_id = 143
);

5.2 列子查询

与行子查询不同，列子查询的结果是一个多行一列的数据列表，所有数据均是同一个字段的数据

# 案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

# 案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT last_name, employee_id, job_id, salary FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) 
AND job_id <> 'IT_PROG';

# 案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT last_name, employee_id, job_id, salary FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) 
AND job_id <> 'IT_PROG';

5.3 行子查询

子查询的部分的返回结果是一个一行多列或者多行多列的结果，不同的数据是属于不同的字段的

# 案例：查询员工编号最小并且工资最高的员工信息
SELECT * FROM employees
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

5.4 select + 子查询

# 案例：查询每个部门的部门信息和员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

5.5 from + 子查询

from+子查询在实际应用中很常见

# 案例：查询每个部门的平均工资的工资等级
# 步骤①：查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

# 步骤②：连接步骤①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT  ag_dep.*, g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

5.6 exist + 子查询

exists(完整的查询语句)结果：1或0

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

5.7 case + 子查询

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;

6 窗口函数

窗口函数可以分为五类，分别是序号函数、分布函数、前后函数、收尾函数和其他类型的函数。详细介绍可以看官网：此外，在一个窗口内也可以结合聚合函数使用，如max、min、count、avg等。窗口函数的基本语法为：

window_function_name(expr) OVER([window_name] [partition_clause][order_clause] [frame_clause]);

6.1 序号函数

ROW_NUMBER(): 对每一个窗口中的数据的给一个序号进行顺序显示。

# 对goods表按照商品分类category_id进行分窗口，在每一个窗口中按照价格price进行降序排序
SELECT 
	ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
	id, category_id, category, NAME, price, stock
FROM goods;

RANK函数: 对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

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()：对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

SELECT 
	DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
	id, category_id, category, NAME, price, stock
FROM goods;

6.2 分布函数

PERCENT_RANK()：百分比函数
CUME_DIST()：百分比累积函数。主要用于查询小于或等于某个值的比例，是PERCENT_RANK()函数的累积值。

SELECT 
	RANK() 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;

6.3 前后函数

LAG(expr,n)：返回当前行的前n行的expr的值，如果没有的话则为null。可以用来计算留存率
LEAD(expr,n)：返回当前行的后n行的expr的值，如果没有的话则为null。

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_price
    FROM goods 
    WINDOW w AS (PARTITION BY category_id ORDER BY price) # 声明窗口
) t;

6.4 首尾函数

FIRST_VALUE(expr)：返回一个窗口内的第一个expr的值。
LAST_VALUE(expr)：返回一个窗口内最后一个expr的值。
NTH_VALUE(expr, n)和NTILE(n)同理

SELECT 
id, category, NAME, price, stock,
FIRST_VALUE(price) OVER w AS first_price
FROM goods 
WINDOW w AS (PARTITION BY category_id ORDER BY price);

6.5 frame子句

window_function_name(expr) OVER([window_name] [partition_clause][order_clause] [frame_clause]);

frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。 frame类型

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
UNBOUNDED PRECEDING 边界是分区中的第一行
UNBOUNDED FOLLOWING 边界是分区中的最后一行
<expr> PRECEDING 边界是当前行减去expr的值
<expr> FOLLOWING 边界是当前行加上expr的值

例如：

rows between 2 preceding and current row # 取本行和前面两行
rows between unbounded preceding and current row # 取本行和之前所有的行
rows between current row and unbounded following # 取本行和之后所有的行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行

# 案例：基于员工按时间排序计算 每年及前后一年 销售额的平均值
select
    s.sales_employee,
    avg(s.sale) over (
      partition by s.sales_employee
      ORDER by s.fiscal_year 
      -- 基于行进行分窗，格式如下：
      -- 格式：ROWS BETWEEN <expr> AND <expr>
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg3
from
sales s;

标签：name,employees,汇总,查询,department,MySQL,DQL,id,SELECT
From： https://blog.51cto.com/u_14065757/6517250

MySQL DQL语法汇总