MySQL - WITH...AS 创建临时表复用子查询_mysqlwithas调用二次

当我们遇到复杂 SQL 子查询的时候，发现表经常被复用的情况，按照传统的写法，就需要子查询 SQL 重复写，这样 SQL 很不优雅，今天我们用 WITH AS 来解决这个问题！（MySQL 8.0+ 开始支持）

WITH t2 AS(
    WITH t1 AS(
        SELECT id, visit_date, people FROM Stadium WHERE people >= 100
    )
    SELECT id, visit_date, people, @gid:=IF(id - @preId = 1, @gid, @gid+1) groupId, @preId:=id
    FROM t1, (SELECT @preId:=(SELECT id FROM t1 LIMIT 0, 1), @gid:=0) init
)
 
SELECT id, visit_date, people
FROM t2
WHERE groupId IN (
    SELECT groupId
    FROM t2
    GROUP BY groupId
    HAVING COUNT(groupId) >= 3
)

我们可以看到好多地方用到了 t1 和 t2，如果都采取传统子查询写法，我估计你要奔溃，而且没自信是否能一把过！

这里还有个地方需要注意，这里的临时表有 2 个，除了上面的嵌套的写法外，还可以这样写：

WITH
cte1 AS
(
    select * from table1 where name like 'abc%'
),
cte2 AS
(
    select * from table2 where id > 20
),
cte3 AS
(
    select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

• 这样有一个好处是：可以多个临时表被最后的 SELECT 语句复用，否则嵌套里面的临时表是无法被最后的 SELECT 复用的。 

 但是绝对不能下面这种写法：

// 反例: 运行错误
WITH t1 AS(
    SELECT id, visit_date, people FROM Stadium WHERE people >= 100
)
 
WITH t2 AS(
    SELECT id, visit_date, people, @gid:=IF(id - @preId = 1, @gid, @gid+1) groupId, @preId:=id
    FROM t1, (SELECT @preId:=(SELECT id FROM t1 LIMIT 0, 1), @gid:=0) init
)