MySQL中WITH AS用法

什么是WITH AS语句

WITH AS语句是MySQL中一种比较高级的、复杂查询中的一种语法。它可以在一次查询中，将多个子查询合并为一个临时的表，并且可以对这个表进行操作，让查询变得更加高效。

使用WITH AS语句的示例

下面我们来看一个使用WITH AS语句的示例。假设我们有以下两个表，一个是学生表，一个是成绩表：

学生表（student）：

| id | name | gender | age |

|----|--------|--------|-----|

| 1 | Alice | F | 18 |

| 2 | Bob | M | 19 |

| 3 | Carol | F | 20 |

| 4 | David | M | 21 |

| 5 | Emily | F | 22 |

成绩表（score）：

| id | course | score | student_id |

|----|--------|-------|------------|

| 1 | Math | 85 | 1 |

| 2 | Math | 90 | 2 |

| 3 | Math | 88 | 3 |

| 4 | Math | 80 | 4 |

| 5 | Math | 92 | 5 |

| 6 | English| 92 | 1 |

| 7 | English| 88 | 2 |

| 8 | English| 88 | 3 |

| 9 | English| 90 | 4 |

|10 | English| 85 | 5 |

现在我们要查询每个学生的平均成绩和排名，我们可以使用WITH AS语句来构造一个临时表，然后在这个临时表上进行操作，如下所示：

WITH tmp AS (
    SELECT student_id, AVG(score) AS avg_score
    FROM score
    GROUP BY student_id
)
SELECT s.name, t.avg_score, @rank := @rank + 1 AS rank
FROM student s, tmp t, (SELECT @rank := 0) r
WHERE s.id = t.student_id
ORDER BY t.avg_score DESC;

在以上的SQL语句中，我们首先使用了WITH AS语句，构造了一个名为tmp的临时表。这个临时表包含了每个学生的平均成绩。然后，我们在临时表tmp和学生表student上进行了一个JOIN操作，将平均成绩和对应的学生姓名、学生ID等信息整合起来，最后输出每个学生的姓名、平均成绩和排名。

使用WITH AS语句的好处

使用WITH AS语句可以将多个子查询合并为一个临时的表，可以避免多次扫描同一张表，提高了查询效率。此外，它还可以提高代码的可读性。将多个子查询写在同一个SQL语句中，可以使代码更加简洁清晰，易于维护。

总结

WITH AS语句是MySQL中一种比较高级的、复杂查询中的一种语法。它可以将多个子查询合并为一个临时的表，并且可以对这个表进行操作，让查询变得更加高效。使用WITH AS语句可以提高查询效率同时使代码更加清晰易于维护。