襄阳蒂凯网络网站建设小程序,餐饮店会员卡管理系统,宿州网站建设价格,域名注册需要哪些条件文章目录 新特性#xff1a;公用表表达式(cte)普通公用表表达式递归公用表表达式小 结 新特性#xff1a;公用表表达式(cte)
公用表表达式#xff08;或通用表表达式#xff09;简称为CTE#xff08;Common Table Expressions#xff09;。CTE是一个命名的临时结果集公用表表达式(cte)普通公用表表达式递归公用表表达式小 结 新特性公用表表达式(cte)
公用表表达式或通用表表达式简称为CTECommon Table Expressions。CTE是一个命名的临时结果集作用范围是当前语句。CTE可以理解成一个可以复用的子查询当然跟子查询还是有点区别的CTE可以引用其他CTE但子查询不能引用其他子查询。所以可以考虑代替子查询。
依据语法结构和执行方式的不同公用表表达式分为 普通公用表表达式 和 递归公用表表达式 2 种。
普通公用表表达式
普通公用表表达式的语法结构是
WITH CTE名称
AS 子查询
SELECT|DELETE|UPDATE 语句;普通公用表表达式类似于子查询不过跟子查询不同的是它可以被多次引用而且可以被其他的普通公用表表达式所引用。
举例查询员工所在的部门的详细信息。
SELECT * FROM departmentsWHERE department_id IN (SELECT DISTINCT department_id
FROM employees
);
/* --------------- ------------------ ------------ -------------
| department_id | department_name | manager_id | location_id |--------------- ------------------ ------------ -------------
| 10 | Administration | 200 | 1700 |
| 20 | Marketing | 201 | 1800 |
| 30 | Purchasing | 114 | 1700 |
| 40 | Human Resources | 203 | 2400 |
| 50 | Shipping | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
| 90 | Executive | 100 | 1700 |
| 100 | Finance | 108 | 1700 |
| 110 | Accounting | 205 | 1700 |--------------- ------------------ ------------ ------------- */这个查询也可以用普通公用表表达式的方式完成
WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
SELECT *
FROM departments d JOIN emp_dept_id e ON d.department_id e.department_id;
/* --------------- ------------------ ------------ ------------- ---------------
| department_id | department_name | manager_id | location_id | department_id |--------------- ------------------ ------------ ------------- ---------------
| 90 | Executive | 100 | 1700 | 90 |
| 60 | IT | 103 | 1400 | 60 |
| 100 | Finance | 108 | 1700 | 100 |
| 30 | Purchasing | 114 | 1700 | 30 |
| 50 | Shipping | 121 | 1500 | 50 |
| 80 | Sales | 145 | 2500 | 80 |
| 10 | Administration | 200 | 1700 | 10 |
| 20 | Marketing | 201 | 1800 | 20 |
| 40 | Human Resources | 203 | 2400 | 40 |
| 70 | Public Relations | 204 | 2700 | 70 |
| 110 | Accounting | 205 | 1700 | 110 |--------------- ------------------ ------------ ------------- --------------- */例子说明公用表表达式可以起到子查询的作用。以后如果遇到需要使用子查询的场景你可以在查询之前先定义公用表表达式然后在查询中用它来代替子查询。而且跟子查询相比公用表表达式有一个优点就是定义过公用表表达式之后的查询可以像一个表一样多次引用公用表表达式而子查询则不能。
递归公用表表达式
递归公用表表达式也是一种公用表表达式只不过除了普通公用表表达式的特点以外它还有自己的特点就是可以调用自己。它的语法结构是
WITH RECURSIVE
CTE名称 AS 子查询
SELECT|DELETE|UPDATE 语句;递归公用表表达式由 2 部分组成分别是种子查询和递归查询中间通过关键字 UNION [ALL]进行连接。这里的种子查询意思就是获得递归的初始值。这个查询只会运行一次以创建初始数据集之后递归查询会一直执行直到没有任何新的查询数据产生递归返回。
案例 针对于我们常用的employees表包含employee_idlast_name和manager_id三个字段。如果a是b的管理者那么我们可以把b叫做a的下属如果同时b又是c的管理者那么c就是b的下属是a的下下属。
下面尝试用查询语句列出所有具有下下属身份的人员信息。
如果用我们之前学过的知识来解决会比较复杂至少要进行 4 次查询才能搞定
第一步先找出初代管理者就是不以任何别人为管理者的人把结果存入临时表 第二步找出所有以初代管理者为管理者的人得到一个下属集把结果存入临时表 第三步找出所有以下属为管理者的人得到一个下下属集把结果存入临时表。
第四步找出所有以下下属为管理者的人得到一个结果集。
如果第四步的结果集为空则计算结束第三步的结果集就是我们需要的下下属集了否则就必须继续进行第四步一直到结果集为空为止。比如上面的这个数据表就需要到第五步才能得到空结果集。而且最后还要进行第六步把第三步和第四步的结果集合并这样才能最终获得我们需要的结果集。
如果用递归公用表表达式就非常简单了。我介绍下具体的思路。
用递归公用表表达式中的种子查询找出初代管理者。字段 n 表示代次初始值为 1表示是第一代管理者。
用递归公用表表达式中的递归查询查出以这个递归公用表表达式中的人为管理者的人并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了递归返回。
在最后的查询中选出所有代次大于等于 3 的人他们肯定是第三代及以上代次的下属了也就是下下属了。这样就得到了我们需要的结果集。
这里看似也是 3 步实际上是一个查询的 3 个部分只需要执行一次就可以了。而且也不需要用临时表保存中间结果比刚刚的方法简单多了。
WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n
FROM employees WHERE employee_id 100-- 种子查询找到第一代领导
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n1
FROM employees AS a JOIN cte ON (a.manager_id cte.employee_id)-- 递归查询找出以递归公用表表达式的人为领导的人
)SELECT employee_id,last_name FROM cte WHERE n 3;总之递归公用表表达式对于查询一个有共同的根节点的树形结构数据非常有用。它可以不受层级的限制轻松查出所有节点的数据。如果用其他的查询方式就比较复杂了。
小 结
公用表表达式的作用是可以替代子查询而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效可以轻松搞定其他查询方式难以处理的查询。
