三表关联生成影视统计报告：我的SQL写法是否正确？

现有三个数据表：

• Movie表
  • id：电影ID
  • title：电影名称
• Actor表
  • id：演员ID
  • title：演员姓名
• Statistics表（存储统计数据）
  • movie_id：电影ID
  • actor_id：演员ID
  • year：年份
  • month：月份
  • day：日期
  • total_pc：好评影评人数
  • total_nc：差评影评人数
  • total_revenue：总营收

需要生成包含以下字段的报告：

• Month（月份）
• Day（日期）
• Name of Movie（电影名称）
• Name of Actor（演员姓名）
• Total Positive Critics（好评影评总数）
• Total Negative Critics（差评影评总数）
• Total Revenue（总营收）

查询要求：仅提取标题含字母“S”的电影数据，且日期范围为2013年8月28日至2014年9月5日。

我编写的SQL语句如下：

UPDATE setlists
SET full_date = STR_TO_DATE(CONCAT(year,'-',month,'-',day), '%Y-%m-%d');

SELECT S.month AS Month, S.day AS Day, M.title AS Name_of_Movie,  A.title AS Name_of_Actor, S.total_pc  AS Total_Positive_Critics, S.total_nc AS Total_Negative_Critics, S.total_revenue AS Total_Revenue
FROM Statistics S
JOIN Movie M ON M.id = S.movie_id
JOIN Actor A ON A.id = S.app_id
WHERE S.full_date BETWEEN '2013-08-28' AND '2014-09-05' AND C.title LIKE '%S%'

请问我的思路是否正确？或者有没有更优的实现方式？

你的SQL存在几个明显错误，先逐一指出：

1. UPDATE语句目标表错误：你更新的是setlists表，但实际需要处理的是Statistics表；而且没必要提前修改原表新增full_date字段，直接在查询中临时拼接日期即可，避免改动原始数据。
2. 关联字段名错误：关联Actor表时用了S.app_id，但Statistics表中对应的关联字段是actor_id，这里字段名写错了。
3. WHERE子句别名错误：条件里用了C.title，但没有定义别名C，要筛选电影标题含“S”，应该用M.title（Movie表的别名是M）。

修正后的SQL（无需修改原表） #

SELECT 
    S.month AS Month,
    S.day AS Day,
    M.title AS `Name of Movie`,
    A.title AS `Name of Actor`,
    S.total_pc AS `Total Positive Critics`,
    S.total_nc AS `Total Negative Critics`,
    S.total_revenue AS `Total Revenue`
FROM Statistics S
JOIN Movie M ON M.id = S.movie_id
JOIN Actor A ON A.id = S.actor_id
WHERE 
    STR_TO_DATE(CONCAT(S.year, '-', S.month, '-', S.day), '%Y-%m-%d') BETWEEN '2013-08-28' AND '2014-09-05'
    AND M.title LIKE '%S%';

优化建议 #

如果这个查询需要频繁执行，建议通过以下方式提升性能：

1. 给Statistics表新增full_date日期类型字段并初始化数据：

ALTER TABLE Statistics ADD COLUMN full_date DATE;
UPDATE Statistics SET full_date = STR_TO_DATE(CONCAT(year, '-', month, '-', day), '%Y-%m-%d');

2. 创建联合索引加速查询：

CREATE INDEX idx_stat_date_movie ON Statistics(full_date, movie_id);
CREATE INDEX idx_movie_title ON Movie(title);

之后查询直接用S.full_date做条件过滤，性能会显著提升。

内容的提问来源于stack exchange，提问作者FernandoAnalytics