CTE 简介 (WITH)

Introduction to CTEs (WITH)

Sooner or later it happens to everyone: you need to filter data based on the result of an aggregation, then take that filter and JOIN it with another aggregated query. You start writing a SELECT inside a SELECT, which in turn ends up in the FROM of yet another SELECT.

The result? Spaghetti SQL, a query that's unreadable for any human being.

CTEs (Common Table Expressions) let you untangle this mess. You activate them with the WITH keyword, which allows you to define temporary named queries and then reuse them as "virtual tables" in the final query.

Basic Syntax

WITH premium_customers AS (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total_amount) > 1000
)
SELECT customers.first_name, premium_customers.total_spent
FROM customers
JOIN premium_customers ON customers.id = premium_customers.customer_id;

As you can see, the main query at the bottom reads from premium_customers as if it were a real table! And it's much more readable than a mega inner-join with nested subqueries.