第 5 站练习答案

← 本站索引

每组 Top-N

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY total_amount DESC, order_id
         ) AS rn
  FROM sales_order AS o
)
SELECT * FROM ranked WHERE rn <= 3;

ROW_NUMBER 保证每组至多三行；若并列金额都应保留，改用 RANK 或 DENSE_RANK，并接受结果可能超过三行。

JSON 校验

WITH payload AS (
  SELECT *
  FROM JSON_TABLE(
    '[{"sku":"BOOK-001","quantity":2},{"sku":"UNKNOWN","quantity":1}]',
    '$[*]' COLUMNS (
      sku VARCHAR(40) PATH '$.sku',
      quantity INT PATH '$.quantity'
    )
  ) AS jt
)
SELECT p.sku
FROM payload AS p
LEFT JOIN product AS pr ON pr.sku = p.sku
WHERE pr.product_id IS NULL;

该查询把外部结构转为明确列后再验证引用。

加码题解：CTE、窗口与 JSON

WITH ranked AS (
  SELECT customer_id, id,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, id DESC) rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

关键推理

窗口函数保留行粒度，排名条件在外层过滤；CTE 让每一步的语义可命名。

可替代方案

对热点 JSON 路径增加生成列和索引，而不是每次全表解析。

典型错误

窗口排序不唯一造成结果抖动，或以为 CTE 必然物化/更快。

复盘时请附上版本、隔离级别、执行计划或恢复位置中的相关证据；没有证据的“看起来正确”不算完成。