CTE 公用表表达式：把一条大 SQL 写成一条流水线

SELECT * FROM (
  SELECT e.*, t.avg_plays
  FROM episodes e
  JOIN (
    SELECT show_id, AVG(play_count) AS avg_plays
    FROM episodes
    GROUP BY show_id
  ) t ON t.show_id = e.show_id
  WHERE t.avg_plays > 40000
) result
ORDER BY result.avg_plays DESC;

WITH show_avg AS (                       -- 第 1 步：每个节目的平均播放量
  SELECT show_id, AVG(play_count) AS avg_plays
  FROM episodes
  GROUP BY show_id
),
good_shows AS (                          -- 第 2 步：筛出优质节目
  SELECT show_id, avg_plays
  FROM show_avg
  WHERE avg_plays > 40000
)
SELECT e.*, g.avg_plays                  -- 第 3 步：拉出全部单集
FROM episodes e
JOIN good_shows g ON g.show_id = e.show_id
ORDER BY g.avg_plays DESC;

WITH hit_episodes AS (
  SELECT id, title, play_count
  FROM episodes
  WHERE play_count >= 20000
)
SELECT * FROM hit_episodes ORDER BY play_count DESC;

WITH
city_stats AS (
  SELECT city, COUNT(*) AS listener_cnt
  FROM listeners
  GROUP BY city
),
big_city AS (
  SELECT city FROM city_stats WHERE listener_cnt >= 2   -- 引用了 city_stats
)
SELECT b.city, c.listener_cnt
FROM big_city b
JOIN city_stats c ON c.city = b.city;

WITH monthly AS (
  SELECT DATE_TRUNC('month', published_at)::DATE AS month,
         SUM(play_count) AS plays
  FROM episodes
  GROUP BY 1
)
SELECT
  cur.month,
  cur.plays  AS this_month,
  prev.plays AS last_month,
  cur.plays - prev.plays AS mom_diff
FROM monthly cur
LEFT JOIN monthly prev
  ON prev.month = cur.month - INTERVAL '1 month';

WITH top2(show_id, avg_plays) AS (
  SELECT show_id, AVG(play_count)
  FROM episodes GROUP BY show_id
  ORDER BY 2 DESC LIMIT 2
)
SELECT * FROM top2;

-- PG 12+ 默认内联：expensive 会被执行两次
WITH expensive AS (
  SELECT * FROM episodes WHERE play_count > 20000   -- 假设这个过滤很贵
)
SELECT * FROM expensive WHERE show_id = 1
UNION ALL
SELECT * FROM expensive WHERE show_id = 4;

WITH expensive AS MATERIALIZED (
  SELECT * FROM episodes WHERE play_count > 20000
)
SELECT * FROM expensive WHERE show_id = 1
UNION ALL
SELECT * FROM expensive WHERE show_id = 4;

WITH stats AS (
  SELECT show_id, AVG(play_count) AS avg_plays
  FROM episodes GROUP BY show_id
)
SELECT /*+ NO_MERGE(stats) */ * FROM stats WHERE avg_plays > 40000;

-- INSERT：把 8 月上架的单集补进周榜候选表 weekly_pool（PostgreSQL，含防重）
-- 假设该表已存在且 episode_id 上有唯一约束
WITH august AS (
  SELECT id, title FROM episodes
  WHERE published_at >= '2026-08-01' AND published_at < '2026-09-01'
)
INSERT INTO weekly_pool (episode_id, title)
SELECT id, title FROM august
ON CONFLICT (episode_id) DO NOTHING;

-- UPDATE：把低播放单集下架（PostgreSQL 的 UPDATE ... FROM 写法）
WITH cold AS (
  SELECT id FROM episodes WHERE play_count < 10000
)
UPDATE episodes e
SET duration_min = NULL
FROM cold
WHERE e.id = cold.id;

-- 同一件事 MySQL 8.0 的写法（没有 UPDATE ... FROM，用 IN 子查询）
WITH cold AS (
  SELECT id FROM episodes WHERE play_count < 10000
)
UPDATE episodes
SET duration_min = NULL
WHERE id IN (SELECT id FROM cold);

-- DELETE：删掉没有任何评分的单集（两个方言通用）
WITH no_rating AS (
  SELECT e.id
  FROM episodes e
  LEFT JOIN ratings r ON r.episode_id = e.id
  WHERE r.episode_id IS NULL
)
DELETE FROM episodes WHERE id IN (SELECT id FROM no_rating);

-- 规则 1：作用域只有一条语句。下一条语句里 top2 已经不存在
WITH top2 AS (SELECT 1 AS x) SELECT * FROM top2;
SELECT * FROM top2;   -- ERROR: relation "top2" does not exist

-- 规则 2：后面的 CTE 能引用前面的，反之不行
WITH a AS (SELECT 1 AS x),
     b AS (SELECT x + 1 AS y FROM a)   -- 合法：b 引用 a
SELECT * FROM b;

-- 规则 3：普通 CTE 不能引用自己，自引用必须加 RECURSIVE
WITH c AS (SELECT * FROM c)            -- ERROR
SELECT * FROM c;

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * FROM counter;