sql with as用法详解

下面是SQL WITH AS用法的详解攻略，内容包括WITH AS的含义、语法、用法、示例等方面。

含义

WITH AS是一种用于创建临时表格的SQL 语句。它可以改善可读性，包含在它中的代码块可以使查询更加优雅清晰。

语法

WITH AS语句由两个部分组成：

• 第一部分是WITH关键字。后面紧跟着一个或多个定义代表临时表格名称（也叫作查询块）的逗号分隔子句。
• 第二部分是SELECT语句，从这个语句中派生出临时表格。

语法如下：

WITH temp_table (column_1, column_2, ...) AS  
(SELECT column_1, column_2, ...  
FROM table_name)  
SELECT *  
FROM temp_table;

其中，temp_table是临时表格名称，(column_1, column_2, ...)是临时表格的列名，table_name是原始表格的名称，*表示选取表格中所有的列。需要注意的是，SELECT语句中的列名和临时表格中的列名需要对应。

用法

WITH AS可用于以下场景：

• 重新利用同一个子查询
• 将复杂的操作分解为多个可读的模块

使用临时表格通常可以减少代码的重复编写，提高查询的可读性、可维护性和效率。

示例

下面是两个使用WITH AS语句的示例，分别说明对于某些复杂查询，该语句可以使查询更加直观、简洁和可读。

示例一

下面的查询将压缩各个州的销售值，并针对甲地、乙地进行比较，查看其销售额的增长情况。这个查询用到了两个WITH AS段，分别是州和销售。

WITH 
州 AS (
  SELECT DISTINCT state, name
  FROM locations
),
销售 AS (
  SELECT 
    客户名称, 
    州, 
    订单日期, 
    SUM(销售额) AS 销售额
  FROM orders
  JOIN 州 ON orders.州名称 = 州.名称
  GROUP BY 客户名称, 州, 订单日期
)
SELECT 
  t1.销售人员姓名, 
  t1.客户名称, 
  t1.州名称 AS '甲地', 
  t2.州名称 AS '乙地', 
  t1.销售额 AS '2021年销售额', 
  t2.销售额 AS '2022年销售额'
FROM 
  销售 t1 
  JOIN 销售 t2 ON t1.客户名称 = t2.客户名称 
    AND t1.订单日期 < t2.订单日期 
  JOIN 州 AS s1 ON t1.州 = s1.州 
  JOIN 州 AS s2 ON t2.州 = s2.州 
WHERE s1.state = '甲州' 
  AND s2.state = '乙州';

示例二

下面的查询从score_list表格中获取每个班级每个学生的平均分值，并针对所有班级进行排序，查询出平均分数最高的前三个班级。

WITH 
  classes AS (SELECT DISTINCT class_id FROM score_list), 
  class_scores AS (
    SELECT 
      class_id, 
      AVG(score) AS avg_score
    FROM score_list 
    GROUP BY class_id
  )
SELECT 
  class_scores.class_id, 
  ROUND(class_scores.avg_score, 2) AS avg_score
FROM 
  class_scores 
  JOIN classes ON classes.class_id = class_scores.class_id 
ORDER BY class_scores.avg_score DESC 
LIMIT 3;

以上就是WITH AS用法的详细攻略。希望对你有所帮助。