SQL 的 with as 临时中间表

提示

Hive SQL 教程欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《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 临时中间表

语法

案例

语句的优点

相关内容