Bài học theo mô-đun (2/3)
CTE nhiều và được xâu chuỗi
Multiple and Chained CTEs
The elegance of WITH expressions really shines when you chain several together. You can define many CTEs in a row, separating them with a comma.
And here's something that refactoring purists love: later CTEs can read data from earlier CTEs, unpacking sequential calculations piece by piece!
Multi-CTE Example
WITH premium_customers AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
),
top_customers AS (
SELECT * FROM premium_customers
WHERE total_spent > 5000
)
SELECT c.first_name, tc.total_spent
FROM customers c
JOIN top_customers tc ON c.id = tc.customer_id;It's block-style programming! Each block does just one job.
Define two sequential CTEs: \n1) 'order_totals' computes 'customer_id' and the 'order_count' placed using `COUNT(*)` from 'orders'.\n2) 'gold_users' reads from 'order_totals' filtering customers with 'order_count >= 2'.\nFinally JOIN the 'customers' table (extracting 'first_name' and 'last_name') with 'gold_users' (ON c.id = g.customer_id), and also show their 'order_count'.
Hiển thị gợi ý
Separate order_totals and gold_users with a comma (no second WITH!)
Giải pháp khả dụng sau 3 lần thử
Now let's use multiple CTEs to simplify metrics: Create 'max_price' (extracts a single row with 'max_value' using `MAX(unit_price)` from 'order_items'). In the main query, extract every 'product_id' and 'unit_price' from 'order_items' and show how much it differs from the maximum price, computing '(SELECT max_value FROM max_price) - unit_price AS difference'. No Join — use a subquery on the CTE to get a single value!
Hiển thị gợi ý
WITH max_price AS (...) SELECT product_id, unit_price, (SELECT max_value FROM max_price) - unit_price FROM order_items;
Giải pháp khả dụng sau 3 lần thử