SQL 的 with as 临时中间表

更新时间：2021-06-26 14:05:27 标签：sql 查询

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。

目前 oracle、sql server、hive、MySQL8.0 等均支持 with as 用法。

语法

它的语法为：

-- with table_name as(子查询语句) 其他sql
with temp as (
    select * from xxx
)
select * from temp;

如果定义了 with 子句，但其后没有跟 select 查询，则会报错（没有使用没关系，其后必须有 select），因此不允许单独使用。

如果要定义多个临时子查询：

WITH t1 AS (
        SELECT *
        FROM abc
    ),
    t2 AS (
        SELECT *
        FROM efg
    )
SELECT *
FROM t1, t2

前面的 with 子句定义的查询可以后边 with 子句中使用，但一个 with 子句内部不能嵌套 with 子句。

with t1 as (select * from abc),
t2 as (select t1.id from t1)
select * from t2

它只能在一条sql中使用。

案例

对考试成绩在本班平均成绩 75-80 的加 5 分，80 以上的加 10 分，其他的不加，实现代码为：

with temp_tb as (
    SELECT
        id,
        name,
        class,
        math,
        MAX(math) over(PARTITION by class) as max_math -- 每组最大值
    FROM
        students
)

SELECT *,
    CASE
        when max_math between 75 and 80 then math+5
        when max_math > 80 then math+10
        ELSE max_math
    END as rated_math

FROM
    temp_tb
'''
id|name|class|math|max_math|rated_math|
--+----+-----+----+--------+----------+
 1|张涛  |    1|  66|      78|        71|
 3|赵丹丹 |    1|  55|      78|        60|
 6|田迪  |    1|  78|      78|        83|
 8|周平  |    1|  77|      78|        82|
 2|王琳  |    2|  88|      88|        98|
 4|李成  |    2|  54|      88|        64|
 7|王卫栋 |    2|  88|      88|        98|
 5|赵天成 |    3|  77|      78|        82|
 9|武明  |    3|  78|      78|        83|
'''

语句的优点

提高代码可读性(结构清晰)
简化sql,优化执行速度(with子句只需要执行一次)

SQL 的 with as 临时中间表

语法

案例

语句的优点

相关内容