翼度科技»论坛 编程开发 mysql 查看内容

MySQL之使用WITH子句和临时表达式进行数据分析和筛选方式

9

主题

9

帖子

27

积分

新手上路

Rank: 1

积分
27
使用WITH子句和临时表达式进行数据分析和筛选

MySQL支持使用
  1. WITH
复制代码
创建临时表达式,通常称为"Common Table Expressions"(CTE)。
CTE 通常用于较复杂的查询,为复杂查询提供了一种更清晰、模块化的方式,以提高复杂查询的可读性和易维护性。

举个通用的例子
  1. my_cte
复制代码
是一个临时表达式,它包含了从
  1. my_table
复制代码
中选择满足某个条件的列,然后,在查询的其余部分中引用这个临时表达式
  1. WITH my_cte AS (
  2.     SELECT
  3.         column1,
  4.         column2
  5.     FROM
  6.         my_table
  7.     WHERE
  8.         condition
  9. )
  10. SELECT
  11.     *
  12. FROM
  13.     my_cte
  14. WHERE
  15.     column1 > 10;
复制代码
  1. WITH
复制代码
子句定义的CTE只在查询执行期间存在,不会在数据库中永久存储。
在使用时,需确保自己的MySQL版本支持
  1. WITH
复制代码
语句,因为不同的MySQL版本可能对此支持程度有所不同。

举个实际的例子

假设有一个包含员工信息的表
  1. employee
复制代码
  1. CREATE TABLE employee (
  2.     emp_id INT,
  3.     emp_name VARCHAR(50),
  4.     emp_salary DECIMAL(10, 2),
  5.     department_id INT
  6. );

  7. INSERT INTO employee VALUES
  8. (1, 'John Doe', 50000.00, 1),
  9. (2, 'Jane Smith', 60000.00, 2),
  10. (3, 'Bob Johnson', 75000.00, 1),
  11. (4, 'Alice Williams', 80000.00, 2);
复制代码
现在,需要计算每个部门的平均工资,并只选择部门平均工资高于整体平均工资的部门员工记录,可以使用
  1. WITH
复制代码
子句来实现:
  1. WITH department_avg_salary AS (
  2.     SELECT
  3.         department_id,
  4.         AVG(emp_salary) AS avg_salary
  5.     FROM
  6.         employee
  7.     GROUP BY
  8.         department_id
  9. )
  10. SELECT
  11.     e.emp_id,
  12.     e.emp_name,
  13.     e.emp_salary,
  14.     e.department_id
  15. FROM
  16.     employee e
  17. JOIN
  18.     department_avg_salary d ON e.department_id = d.department_id
  19. WHERE
  20.     d.avg_salary > (SELECT AVG(emp_salary) FROM employee);
复制代码
在上述例子中,
  1. department_avg_salary
复制代码
是一个CTE,它计算每个部门的平均工资。
然后,通过
  1. JOIN
复制代码
  1. employee
复制代码
表与这个临时表连接,并在最后的
  1. WHERE
复制代码
子句中过滤出部门平均工资高于整体平均工资的记录,输出结果将是符合条件的员工记录
  1. +--------+----------------+------------+---------------+
  2. | emp_id | emp_name       | emp_salary | department_id |
  3. +--------+----------------+------------+---------------+
  4. |      3 | Bob Johnson    |   75000.00 |             1 |
  5. |      4 | Alice Williams |   80000.00 |             2 |
  6. +--------+----------------+------------+---------------+
复制代码
总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/319121wzy.htm
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具