What is a CTE, and how do recursive CTEs work?
What is a CTE, and how do recursive CTEs work?
A common table expression (CTE) is a named temporary result set defined with WITH and visible only to the statement that follows. It makes complex queries readable by splitting them into steps.
WITH monthly AS (
SELECT customer_id, DATE_TRUNC('month', created_at) AS month, SUM(total) AS spent
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
big AS (
SELECT * FROM monthly WHERE spent > 1000
)
SELECT c.name, b.month, b.spent
FROM big b JOIN customers c ON c.id = b.customer_id;
(DATE_TRUNC is PostgreSQL; use DATE_FORMAT in MySQL or DATEFROMPARTS in SQL Server.)
It has an anchor (starting rows), a recursive member that references the CTE itself, and UNION ALL between them. It repeats until the recursive part returns no new rows.
-- number series 1..10
WITH RECURSIVE n(i) AS (
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.