SQL 知识你知道吗：公共表表达式（CTE）

╰半夏微凉° 2024-03-16 17:12 64阅读 0赞

## 一、概念 ##

公共表表达式（CTE）是一种临时命名查询结果集的方式，可以在一个查询语句中多次引用。CTE在查询语句中定义，可以在查询语句中的任何位置使用，并且只在查询语句执行期间存在。CTE通常用于简化复杂的查询，提高查询的可读性和可维护性。CTE的语法如下：

WITH cte\_name (column1, column2, …) AS  
(  
SELECT column1, column2, …  
FROM table\_name  
WHERE condition  
)  
SELECT column1, column2, …  
FROM cte\_name  
WHERE condition

其中，cte\_name是CTE的名称，可以在查询语句的其他部分中使用。column1, column2等是CTE返回的列的名称。SELECT语句定义了CTE的查询结果集。最后的SELECT语句可以引用CTE并过滤、排序、分组等操作。

## 二、实例 ##

一个学生可以参加任意考试，不限次数。

现在我们关注的是每门考试有哪些顶尖的学生。一门考试的 顶尖学生 是指一个学生的分数在参加该考试的 不同 学生中 得分排名前三 。

编写一个SQL查询，找出每个考试中 得分最高的的考生 。

若同一个考生有多条考试记录，则取最高分。

如果存在并列，将并列的考生都列举出来，直到列举的考生达到或超过三人。

以 每门考试考试分数从高到低的顺序 返回结果表。

具体题目和数据集请看这个：[阿里云瑶池数据库SQL挑战赛来袭！Beats耳机等好礼等你赢！][SQL_Beats]

解答：

with cte1 AS 
    (
      select  `studentId` , `testId` ,max(`score`)  as score 
      from `testattempt` 
      GROUP BY  `studentId`, `testId` 
    )
    ,cte2 AS 
    (
    select studentid,testid,`score` ,
    dense_rank() over(PARTITION  by  testid  order by `score`  desc ) as rn
    from cte1
    )
    select a.name,b.name,c.score 
    from cte2 as c
    inner join `student` a on c.studentid = a.`id` 
    inner join `test` b on c.testid = b.`id` 
    where c.rn <=3
    ORDER BY  `testId`,score desc

思路：  
第一步求出每位学生每科的最好成绩  
第二步求出每科的排行  
第三步取每科排行的前三

![在这里插入图片描述][f20956e4b1934c048191a40f818d0a1c.png]

## 三、版本要求 ##

不同数据库对于公共表表达式（CTE）的支持版本不同，以下是一些数据库的要求版本：

*  PostgreSQL：8.4及以上版本支持CTE。
 *  MySQL：8.0及以上版本支持CTE。
 *  SQL Server：2005及以上版本支持CTE。
 *  Oracle：11g及以上版本支持CTE。
 *  DB2：9.7及以上版本支持CTE。
 *  SQLite：3.8.3及以上版本支持CTE。
 *  MariaDB：10.2及以上版本支持CTE。
 *  Amazon Redshift：支持CTE。
 *  Google BigQuery：支持CTE。

需要注意的是，不同版本的数据库可能对于CTE的语法支持程度不同，具体情况需要查看各个数据库的文档。

## 四、副作用 ##

在使用公共表表达式（CTE）时，需要注意以下副作用：

1.  性能问题：CTE可能会导致查询性能问题，特别是在CTE中使用了复杂的查询语句，或者CTE被多次引用时。在使用CTE时，需要仔细评估查询性能，确保查询效率不会受到影响。
2.  内存问题：CTE在内存中存储查询结果集，如果查询结果集很大，可能会导致内存溢出。在使用CTE时，需要仔细评估查询结果集的大小，确保不会出现内存问题。
3.  可读性问题：CTE可能会使查询语句更加复杂，降低查询语句的可读性和可维护性。在使用CTE时，需要考虑查询语句的可读性和可维护性。
4.  数据一致性问题：CTE可能会导致查询结果集与实际数据不一致。在使用CTE时，需要仔细评估查询语句的正确性，确保查询结果集与实际数据一致。

需要根据具体情况评估以上副作用，并在使用CTE时注意避免这些问题的出现。

[SQL_Beats]: https://www.modb.pro/db/636449?utm_source=index_ori
[f20956e4b1934c048191a40f818d0a1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/959b15e5eefc4b90878edbc9738b3b19.png