SQL中的WITH语句详解|极客教程

SQL中的WITH语句详解

在SQL中，WITH语句是一种可以创建临时视图或临时表的功能。它可以让我们在一个查询中定义一个被引用的临时视图或表，然后在查询中使用这个临时视图或表，以简化复杂的查询逻辑。

语法结构

WITH语句的语法结构如下：

WITH 
    cte_name1 AS (SELECT columns FROM table_name1),
    cte_name2 AS (SELECT columns FROM table_name2),
    ...
SELECT columns
FROM cte_name1
JOIN cte_name2
ON condition
...

其中，WITH关键字后面跟着一个逗号分隔的CTE（Common Table Expressions）列表，每个CTE由一个CTE名称和一个查询语句组成。在WITH语句结束后，可以继续进行SELECT、INSERT、UPDATE、DELETE等操作，引用前面定义的CTE。

示例

让我们通过一个简单的示例来说明WITH语句的用法。假设有两个表students和scores，结构如下：

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50),
    major VARCHAR(50)
);

CREATE TABLE scores (
    student_id INT,
    subject VARCHAR(50),
    score INT
);

现在我们希望查询每个学生的总分数和平均分，并按照平均分的高低排序。我们可以使用WITH语句来实现这个查询：

WITH total_scores AS (
    SELECT student_id, SUM(score) AS total_score
    FROM scores
    GROUP BY student_id
),
average_scores AS (
    SELECT student_id, AVG(score) AS average_score
    FROM scores
    GROUP BY student_id
)
SELECT s.student_id, s.student_name, ts.total_score, as.average_score
FROM students s
JOIN total_scores ts ON s.student_id = ts.student_id
JOIN average_scores as ON s.student_id = as.student_id
ORDER BY as.average_score DESC;

在上面的示例中，我们首先定义了两个临时视图total_scores和average_scores，分别计算每个学生的总分数和平均分。然后在主查询中，我们将这两个临时视图与学生表students进行连接，最终按照平均分的高低排序输出。