WITH cte1(txt) AS (SELECT "This "),
cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
cte3(txt) AS (SELECT "nice query" UNION
SELECT "query that rocks" UNION
SELECT "query"),
cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
+----------------------------+----------------------+
| MAX(txt) | MIN(txt) |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)
这个是将临时表列名指定在第一行
WITH RECURSIVE my_cte(a,b,c) AS
(
SELECT 1,1,1
UNION ALL
SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;
这个第一行没有指定列名，然后列名由第一个查询返回结果确定
WITH RECURSIVE my_cte AS
(
SELECT 1 AS a,1 AS b,1 AS c
UNION ALL
SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;
WITH RECURSIVE cte_name [list of column names ] AS
(
SELECT ... <-- specifies initial set
UNION ALL
SELECT ... <-- specifies initial set
UNION ALL
...
SELECT ... <-- specifies how to derive new rows
UNION ALL
SELECT ... <-- specifies how to derive new rows
...
)
[, any number of other CTE definitions ]