MySQL Sakila 与 World 练习答案（一）：S01～S24

对应题目：mysql-sakila-world-query-exercises.md。

分册导航：World W01～W16 · 性能与综合接口

以下使用 MySQL 8.0 和命名参数示意。mysql2 实际默认使用问号占位符；Sequelize replacements 可以使用冒号命名参数。不要把参数直接拼接到 SQL。

S01 查询客户详情

SELECT
  cu.customer_id,
  cu.first_name,
  cu.last_name,
  cu.email,
  cu.active,
  a.address,
  a.district,
  ci.city,
  co.country,
  a.postal_code,
  a.phone
FROM customer AS cu
JOIN address AS a ON a.address_id = cu.address_id
JOIN city AS ci ON ci.city_id = a.city_id
JOIN country AS co ON co.country_id = ci.country_id
WHERE cu.customer_id = :customer_id;

customer_id 是主键，正常最多一行。每个 JOIN 都沿外键方向连接，没有必要使用 DISTINCT。

S02 启用客户前缀列表

SELECT customer_id, first_name, last_name, email, active
FROM customer
WHERE active = 1
  AND last_name LIKE CONCAT(:last_name_prefix, ''%'')
ORDER BY last_name, first_name, customer_id
LIMIT 20;

前缀中的百分号和下划线仍是 LIKE 通配符。如果业务要求把用户输入当作普通文本，需要约定 ESCAPE 并转义它们。

S03 电影筛选列表

SELECT film_id, title, rating, rental_rate, length
FROM film
WHERE rating = :rating
  AND rental_rate BETWEEN :min_rate AND :max_rate
  AND length >= :min_length
ORDER BY rental_rate, film_id
LIMIT 30;

BETWEEN 两端都包含；若产品要求不同边界，应显式使用大于或小于运算符。

S04 分类电影

SELECT f.film_id, f.title, f.rating, f.rental_rate
FROM category AS c
JOIN film_category AS fc ON fc.category_id = c.category_id
JOIN film AS f ON f.film_id = fc.film_id
WHERE c.name = :category_name
ORDER BY f.title, f.film_id;

film_category 的复合主键保证同一电影和分类组合不重复。

S05 演员参演电影

SELECT
  a.actor_id,
  CONCAT_WS('' '', a.first_name, a.last_name) AS actor_name,
  f.film_id,
  f.title,
  f.release_year
FROM actor AS a
JOIN film_actor AS fa ON fa.actor_id = a.actor_id
JOIN film AS f ON f.film_id = fa.film_id
WHERE a.actor_id = :actor_id
ORDER BY f.title, f.film_id;

S06 客户当前未归还电影

SELECT
  r.rental_id,
  r.rental_date,
  f.film_id,
  f.title,
  i.store_id
FROM rental AS r
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film AS f ON f.film_id = i.film_id
WHERE r.customer_id = :customer_id
  AND r.return_date IS NULL
ORDER BY r.rental_date, r.rental_id;

租赁对象是一份 inventory，而不是抽象的 film。

S07 租赁详情

SELECT
  r.rental_id,
  r.rental_date,
  r.return_date,
  CONCAT_WS('' '', cu.first_name, cu.last_name) AS customer_name,
  f.title AS film_title,
  i.store_id,
  CONCAT_WS('' '', s.first_name, s.last_name) AS staff_name
FROM rental AS r
JOIN customer AS cu ON cu.customer_id = r.customer_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film AS f ON f.film_id = i.film_id
JOIN staff AS s ON s.staff_id = r.staff_id
WHERE r.rental_id = :rental_id;

S08 时间范围内支付流水

SELECT payment_id, customer_id, staff_id, rental_id, amount, payment_date
FROM payment
WHERE payment_date >= :start_at
  AND payment_date < :end_at
ORDER BY payment_date DESC, payment_id DESC
LIMIT 50;

左闭右开能自然表达整天和连续分页区间，也避免在索引列上套 DATE 函数。

S09 客户累计消费

要求客户一定返回时：

SELECT
  cu.customer_id,
  COUNT(p.payment_id) AS payment_count,
  COALESCE(SUM(p.amount), 0.00) AS total_amount
FROM customer AS cu
LEFT JOIN payment AS p ON p.customer_id = cu.customer_id
WHERE cu.customer_id = :customer_id
GROUP BY cu.customer_id;

外连接下 COUNT(*) 会把客户占位行计为 1；COUNT(p.payment_id) 只统计真实支付。

S10 时间范围内高消费客户

SELECT
  cu.customer_id,
  CONCAT_WS('' '', cu.first_name, cu.last_name) AS customer_name,
  COUNT(p.payment_id) AS payment_count,
  SUM(p.amount) AS total_amount
FROM customer AS cu
JOIN payment AS p ON p.customer_id = cu.customer_id
WHERE p.payment_date >= :start_at
  AND p.payment_date < :end_at
GROUP BY cu.customer_id, cu.first_name, cu.last_name
HAVING SUM(p.amount) > :minimum_amount
ORDER BY total_amount DESC, cu.customer_id;

WHERE 先过滤原始支付；HAVING 再过滤聚合组。

S11 没有租赁记录的客户

NOT EXISTS 版本：

SELECT cu.customer_id, cu.first_name, cu.last_name, cu.email
FROM customer AS cu
WHERE NOT EXISTS (
  SELECT 1
  FROM rental AS r
  WHERE r.customer_id = cu.customer_id
)
ORDER BY cu.customer_id;

LEFT JOIN 版本：

SELECT cu.customer_id, cu.first_name, cu.last_name, cu.email
FROM customer AS cu
LEFT JOIN rental AS r ON r.customer_id = cu.customer_id
WHERE r.rental_id IS NULL
ORDER BY cu.customer_id;

二者表达相同反连接。实际计划以 EXPLAIN 为准。

S12 没有任何库存的电影

SELECT f.film_id, f.title
FROM film AS f
WHERE NOT EXISTS (
  SELECT 1
  FROM inventory AS i
  WHERE i.film_id = f.film_id
)
ORDER BY f.film_id;

它查询“无库存副本”，不是“库存都借出”。

S13 门店当前可出租库存

SELECT i.inventory_id, i.film_id, f.title, i.store_id
FROM inventory AS i
JOIN film AS f ON f.film_id = i.film_id
WHERE i.store_id = :store_id
  AND NOT EXISTS (
    SELECT 1
    FROM rental AS r
    WHERE r.inventory_id = i.inventory_id
      AND r.return_date IS NULL
  )
ORDER BY f.title, i.inventory_id;

S14 每门店库存与可出租数量

先将粒度保持为“一份库存一行”：

SELECT
  i.store_id,
  COUNT(*) AS total_inventory_count,
  SUM(
    CASE WHEN NOT EXISTS (
      SELECT 1
      FROM rental AS r
      WHERE r.inventory_id = i.inventory_id
        AND r.return_date IS NULL
    ) THEN 1 ELSE 0 END
  ) AS available_inventory_count,
  SUM(
    CASE WHEN EXISTS (
      SELECT 1
      FROM rental AS r
      WHERE r.inventory_id = i.inventory_id
        AND r.return_date IS NULL
    ) THEN 1 ELSE 0 END
  ) AS rented_inventory_count
FROM inventory AS i
GROUP BY i.store_id
ORDER BY i.store_id;

直接 JOIN 全部 rental 历史会把一份库存扩成多行。也可先把未归还 inventory_id 去重后再关联。

S15 逾期未归还

SELECT
  r.rental_id,
  r.customer_id,
  CONCAT_WS('' '', cu.first_name, cu.last_name) AS customer_name,
  f.title AS film_title,
  r.rental_date,
  DATE_ADD(r.rental_date, INTERVAL f.rental_duration DAY)
    AS expected_return_at,
  TIMESTAMPDIFF(
    DAY,
    DATE_ADD(r.rental_date, INTERVAL f.rental_duration DAY),
    CURRENT_TIMESTAMP
  ) AS overdue_days
FROM rental AS r
JOIN customer AS cu ON cu.customer_id = r.customer_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film AS f ON f.film_id = i.film_id
WHERE r.return_date IS NULL
  AND CURRENT_TIMESTAMP >
      DATE_ADD(r.rental_date, INTERVAL f.rental_duration DAY)
ORDER BY expected_return_at, r.rental_id;

TIMESTAMPDIFF(DAY) 计算完整经过的天数；DATEDIFF 只比较日期部分，午夜附近语义不同。

S16 按天统计营业额

SELECT
  DATE(payment_date) AS payment_day,
  COUNT(*) AS payment_count,
  SUM(amount) AS total_amount
FROM payment
WHERE payment_date >= :start_at
  AND payment_date < :end_at
GROUP BY DATE(payment_date)
ORDER BY payment_day;

展示和分组可使用 DATE；范围过滤仍直接作用于原字段。

S17 按月、门店统计营业额

SELECT
  DATE_FORMAT(p.payment_date, ''%Y-%m'') AS payment_month,
  s.store_id,
  COUNT(*) AS payment_count,
  SUM(p.amount) AS total_amount
FROM payment AS p
JOIN staff AS s ON s.staff_id = p.staff_id
WHERE p.payment_date >= :start_at
  AND p.payment_date < :end_at
GROUP BY
  DATE_FORMAT(p.payment_date, ''%Y-%m''),
  s.store_id
ORDER BY payment_month, s.store_id;

年份和月份一起分组，避免不同年份的一月合并。

S18 电影出租次数 Top 10

SELECT f.film_id, f.title, COUNT(r.rental_id) AS rental_count
FROM film AS f
JOIN inventory AS i ON i.film_id = f.film_id
JOIN rental AS r ON r.inventory_id = i.inventory_id
GROUP BY f.film_id, f.title
ORDER BY rental_count DESC, f.film_id
LIMIT 10;

S19 分类营业额

SELECT
  c.category_id,
  c.name AS category_name,
  COUNT(p.payment_id) AS payment_count,
  COALESCE(SUM(p.amount), 0.00) AS total_amount
FROM category AS c
LEFT JOIN film_category AS fc ON fc.category_id = c.category_id
LEFT JOIN inventory AS i ON i.film_id = fc.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
LEFT JOIN payment AS p ON p.rental_id = r.rental_id
GROUP BY c.category_id, c.name
ORDER BY total_amount DESC, c.category_id;

Sakila 中 film_category 每部电影通常只有一个分类，payment.rental_id 也有索引但并非模式层唯一约束。若业务假设一次 rental 只有一笔 payment，应先用数据约束或审计查询验证；多笔支付会被真实累计。

S20 员工和门店收款

SELECT
  s.staff_id,
  CONCAT_WS('' '', s.first_name, s.last_name) AS staff_name,
  s.store_id,
  COUNT(p.payment_id) AS payment_count,
  COALESCE(SUM(p.amount), 0.00) AS total_amount
FROM staff AS s
LEFT JOIN payment AS p
  ON p.staff_id = s.staff_id
 AND p.payment_date >= :start_at
 AND p.payment_date < :end_at
GROUP BY s.staff_id, s.first_name, s.last_name, s.store_id
ORDER BY s.store_id, total_amount DESC, s.staff_id;

时间条件放在 ON 中可保留范围内没有收款的员工；若只需要有收款者，可改 INNER JOIN 并放 WHERE。

S21 每名客户最近一次租赁

WITH ranked_rentals AS (
  SELECT
    r.customer_id,
    r.rental_id,
    r.rental_date,
    f.title,
    ROW_NUMBER() OVER (
      PARTITION BY r.customer_id
      ORDER BY r.rental_date DESC, r.rental_id DESC
    ) AS row_number_in_customer
  FROM rental AS r
  JOIN inventory AS i ON i.inventory_id = r.inventory_id
  JOIN film AS f ON f.film_id = i.film_id
)
SELECT
  cu.customer_id,
  CONCAT_WS('' '', cu.first_name, cu.last_name) AS customer_name,
  rr.rental_id,
  rr.rental_date AS latest_rental_date,
  rr.title AS latest_film_title
FROM customer AS cu
LEFT JOIN ranked_rentals AS rr
  ON rr.customer_id = cu.customer_id
 AND rr.row_number_in_customer = 1
ORDER BY cu.customer_id;

ROW_NUMBER 的第二排序键解决相同时间的确定性。

S22 每分类出租次数最多的三部电影

WITH film_counts AS (
  SELECT
    c.category_id,
    c.name AS category_name,
    f.film_id,
    f.title,
    COUNT(r.rental_id) AS rental_count
  FROM category AS c
  JOIN film_category AS fc ON fc.category_id = c.category_id
  JOIN film AS f ON f.film_id = fc.film_id
  LEFT JOIN inventory AS i ON i.film_id = f.film_id
  LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
  GROUP BY c.category_id, c.name, f.film_id, f.title
),
ranked AS (
  SELECT
    film_counts.*,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY rental_count DESC, film_id
    ) AS position_in_category
  FROM film_counts
)
SELECT category_id, category_name, film_id, title, rental_count
FROM ranked
WHERE position_in_category <= 3
ORDER BY category_id, position_in_category;

ROW_NUMBER 保证每分类最多三行。若要求并列第三名全部进入，使用 DENSE_RANK，但可能超过三行。

S23 高于所属分类平均租金

WITH films_with_average AS (
  SELECT
    c.category_id,
    c.name AS category_name,
    f.film_id,
    f.title,
    f.rental_rate,
    AVG(f.rental_rate) OVER (
      PARTITION BY c.category_id
    ) AS category_average_rate
  FROM category AS c
  JOIN film_category AS fc ON fc.category_id = c.category_id
  JOIN film AS f ON f.film_id = fc.film_id
)
SELECT
  category_id,
  category_name,
  film_id,
  title,
  rental_rate,
  category_average_rate
FROM films_with_average
WHERE rental_rate > category_average_rate
ORDER BY category_id, rental_rate DESC, film_id;

S24 客户累计消费排名

包含没有支付的客户：

WITH customer_totals AS (
  SELECT
    cu.customer_id,
    cu.first_name,
    cu.last_name,
    COALESCE(SUM(p.amount), 0.00) AS total_amount
  FROM customer AS cu
  LEFT JOIN payment AS p ON p.customer_id = cu.customer_id
  GROUP BY cu.customer_id, cu.first_name, cu.last_name
)
SELECT
  customer_id,
  CONCAT_WS('' '', first_name, last_name) AS customer_name,
  total_amount,
  DENSE_RANK() OVER (ORDER BY total_amount DESC) AS amount_rank
FROM customer_totals
ORDER BY amount_rank, customer_id;

先聚合再排名，避免在同一层混用明细聚合和窗口排名。