长SQL之with as 短语

背景：在写多个复杂sql时发现可以用with as

with关键字作用

with as短语，也叫子查询部分，定义一个SQL片段后，该片段可以被整个SQL语句调用

1. 提高SQL可读性，减少嵌套冗余
2. with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。用在UNION ALL的不同部分，作为提供数据的部分（避免重复写相同的SQL片段；提高查询速度）。特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

有效性

hive，Oracle，DB2，SQL SERVER，PostgreSQL都是支持WITH AS 语句进行递归查询。只有MySQL是不支持的

举例说明

1

WITH
    sql1 AS (SELECT to_char(a) s_name FROM test_tempa),  
    sql2 AS (SELECT to_char(b) s_name FROM test_tempb WHERE not exists (SELECT s_name FROM sql1 WHERE rownum=1))  
SELECT * FROM sql1  
UNION ALL 
SELECT * FROM sql2  
UNION ALL 
SELECT 'no records' FROM dual  
 where not exists (SELECT s_name FROM sql1 WHERE rownum=1)  
 and not exists (SELECT s_name FROM sql2 WHERE rownum=1);  

2

WITH special_sales AS (
    SELECT
        *
    FROM
        sales
    WHERE
        price > 90
)


SELECT
    id,
    NAME
FROM
    departments
WHERE
    id IN (
        SELECT
            department_id
        FROM
   special_sales
    )

3

with cte as
(
select Id,Pid,DeptName,0 as lvl from Department
where Id = 2
union all
select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d
on c.Id = d.Pid
)
select * from cte

注意

如果要把with的结果放在in里使用，要这样写：

with tmp as (
	SELECT
		cast( sid AS VARCHAR ) AS server_id 
	FROM
		test.student 
	WHERE
		class = '201' 
		AND type = 0 
		AND is_discard = 0 
		AND create_time >= 1600358400 
		AND create_time <= 1687103999 AND ( sid > 111  OR sid < 2000 )
)

SELECT id,sid FROM table_name WHERE class = '201'  AND dt = '2023-06-18' and reg_server_id in (select server_id from tmp) limit 10

而不是

with tmp as (
	SELECT
		cast( sid AS VARCHAR ) AS server_id 
	FROM
		test.student
	WHERE
		class = '201'
		AND type = 0 
		AND is_discard = 0 
		AND create_time >= 1600358400 
		AND create_time <= 1687103999 AND ( sid > 94000  OR sid < 90000 )
)

SELECT id,sid FROM table_name  WHERE class='201  AND dt = '2023-06-18' and reg_server_id in tmp limit 10

这样写的话会报错的